Select Substring Problem - 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 2005 Forums

Transact-SQL (2005)

Select Substring Problem

Author

Topic

mullanp2
Starting Member

9 Posts

Posted - 2010-07-02 : 09:49:23

Hi all,
I am having difficulty extracting a string from a column (say colA) in all rows.
The column will always have the format \\xxxx_xxx_x\whatiwant\XX000/XX-00XX

whatiwant will always start with either \DS_ \DP_ or \PC_
Now basically I need to do :
@whatiwant = substring (@colName, start of string, lenght of string)

to get the start i used :
patindex('%[DP][CS]%',colA)

to the the end of the string:
I wish there was a INSTR function in SQL so I could use the fourth '\' in the string but there isnt.

I have been using:
patindex('%[^\,^A][\][^D][A-Z,^S][\_,0-9,A-Z][0-9,A-Z]%'colA)+1

the problem is that the format after the fourth '\' is not uniform and there are over 800K rows.
I need a way to extract the string starting with DS_ DP_ or PC_ and ending before the fourth '\'

I have been racking my brain but I am new enough to sql coding and really, really, really, please, please, please need some guidance.

many many thanks in advance,
mul

mullanp2
Starting Member

9 Posts

Posted - 2010-07-02 : 10:02:38

Sorry guys,
I just read the other Select substring query and it works a treat for me.

sorry for wasting your time.

mul

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-02 : 10:04:11

[code]

DECLARE @v varchar(8000); SET @v ='\\xxxx_xxx_x\whatiwant\XX000/XX-00XX'
SELECT SUBSTRING(@v,CHARINDEX('\',@v,3)+1,CHARINDEX('\',@v,(CHARINDEX('\',@v,3)+1)) - (CHARINDEX('\',@v,3)+1))

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-07-02 : 10:15:01

quote:
Originally posted by mullanp2

Sorry guys,
I just read the other Select substring query and it works a treat for me.

sorry for wasting your time.

mul

Was it this one?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146798

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-02 : 10:33:48

quote:
Originally posted by X002548

DECLARE @v varchar(8000); SET @v ='\\xxxx_xxx_x\whatiwant\XX000/XX-00XX'
SELECT SUBSTRING(@v,CHARINDEX('\',@v,3)+1,CHARINDEX('\',@v,(CHARINDEX('\',@v,3)+1)) - (CHARINDEX('\',@v,3)+1))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

If the format is fixed,

DECLARE @v varchar(8000); SET @v ='\xxxx_xxx_x\whatiwant\XX000/XX-00XX'
select PARSENAME(replace(replace(@v,'\\','\'),'\','.'),2)

Madhivanan

Failing to plan is Planning to fail

mullanp2
Starting Member

9 Posts

Posted - 2010-07-05 : 10:36:41

Thanks everyone.
Topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146798 did the trick nicely.

mullanp2
Starting Member

9 Posts

Posted - 2010-07-05 : 10:37:24

Just a quick one.
What it the 't' for at the end of the solution in : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146798

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-05 : 10:40:21

It is the needed ALIAS for the derived table.