String manipulation help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

String manipulation help

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-02-12 : 01:26:36

Hi,

I have a string :
http://mysite/library/faq/article1006.doc

I need to extract the text under the text betweeen / / from thr right hand side

The Output should be FAQ
similarly if the string is
http://mysite/sme/site-sme-spain.doc
I need SME

I tried the below code but doesnt work for all conditions

DECLARE @inputUrl NVARCHAR(100)
DECLARE @catUrl NVARCHAR(100)

SET @catUrl = left(@inputUrl, charindex('.', @inputUrl) - 1)
SET @catUrl = right(@catUrl,charindex('/',@catUrl) - 2)
SET @catUrl = left(@catUrl,charindex('/',@catUrl) - 1)

Help needed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 03:05:25

[code]
select str3 = left(str2, charindex('/', str2) - 1)
from
(
select string, str1,
str2 = right(str1, len(str1) - charindex('/', str1))
from
(
select string,
str1 = right(string, len(string) - charindex('//', string) - 1)
from (
select string = 'http://mysite/sme/site-sme-spain.doc'
) a
) b
)c
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 03:12:11

or make use of the many split function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648


select * from Split('http://mysite/sme/site-sme-spain.doc', '/') where Id = 4

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-02-12 : 03:41:45

Hi,

Thanks for the reply,

The query which you helped me with doesnt work for this

http://gpweb/library/faq/article1006.doc

It gives Library as the output but I need 'FAQ'.

This is what was happening with my query as well.

Please help me.

quote:
Originally posted by khtan

or make use of the many split function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

select * from Split('http://mysite/sme/site-sme-spain.doc', '/') where Id = 4

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 05:59:43

Since you have to calc right to left, make use of my split function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
This function accepts both "from left" and "from right" counting...

SELECT *, dbo.fnParseString(2, '/', YourColumnNameHere)
FROM YourTableNameHere

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources