query to get a part of strin

Question

Post reply

query to get a part of strin

neliii

Say Hey Kid

Points: 701
More actions
May 7, 2010 at 1:07 pm

#218659

column1
\\abc\tri\eds\rf
\\fd\tri\ef\
\\ghij\tri\gf\rf\k
'
'
'
i got some rows like that in a column
now i want to get the result set like
\\abc\tri\eds
\\fd\tri\ef
\\ghij\tri\gf
simply from first '\' to end of 4th '\'
Thnaq in advance for ur help guys...
i'm really learning lot of different stuff from u guys...

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Okay, similar yet different from your other post, so what have you tried to solve this problem?

Cory E. SSCertifiable Points: 7076 More actions · Answer 2

neliii (5/7/2010)
column1
\\abc\tri\eds\rf
\\fd\tri\ef\\ghij\tri\gf\rf\k
'
'
'
i got some rows like that in a column
now i want to get the result set like
\\abc\tri\eds
\\fd\tri\ef
\\ghij\tri\gf
simply from first '\' to end of 4th '\'
Thnaq in advance for ur help guys...
i'm really learning lot of different stuff from u guys...

To follow suit with your other thread you had:

--Code removed to see what OP has done. Thanks Lynn

-- Cory

neliii Say Hey Kid Points: 701 More actions · Answer 3

@lenin

previously i worked on fixed lenth part of string

so i used to use substring and charindex with a particular length

but here the string lenght between 1st'\' and fourth '\' is varying in all the rows ..i'e 1st row string lenght varies with 2nd row and all

so i couldn't figure t out how to use that functions for that type of rows

Cory E. SSCertifiable Points: 7076 More actions · Answer 4

Care to post what you had? I have a solution, it may not be the best (Back to substring over stuff), but I am curious as to what you have.

-- Cory

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Do all the values in the column have at least four \'s?

neliii Say Hey Kid Points: 701 More actions · Answer 6

neliii

Say Hey Kid

Points: 701

May 7, 2010 at 1:34 pm

#1164420

@Lynn

yes lynn

Cory E. SSCertifiable Points: 7076 More actions · Answer 7

Lynn Pettis (5/7/2010)
Do all the values in the column have at least four \'s?

Good question, a follow up question is do you just want to get rid of everything past the last "\"?

String = \\abc\def\ghi\jklmnop

expected result = \\abc\def\ghi

-- Cory

Cory E. SSCertifiable Points: 7076 More actions · Answer 8

DECLARE @STR NVARCHAR(30)

SET @STR = '\\abc\xyz\wzy\123'

SELECT SUBSTRING(@str, 1, LEN(@str) - CHARINDEX('\', REVERSE(@str)))

I am posting my solution.

-- Cory

neliii Say Hey Kid Points: 701 More actions · Answer 9

neliii

Say Hey Kid

Points: 701

May 7, 2010 at 1:38 pm

#1164426

@cory

yes exactly cory

neliii Say Hey Kid Points: 701 More actions · Answer 10

@cory

thanq for ur response

but what if i had

\\abc\xyz\wzy\123\toi

and i want to get only

\\abc\xyz\wzy

Cory E. SSCertifiable Points: 7076 More actions · Answer 11

This is why we are looking for requirements.

-- Cory

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 12

Edit: code removed, valuable lesson in progress. Sorry guys, I worked on this for a while and posted without looking for responses.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

neliii Say Hey Kid Points: 701 More actions · Answer 13

i tried to learn how to get that but couldnt...

please help me out with that

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 14

All we wanted is for you to post the code you have written to try and solve your problem.

Here is my solution using the dbo.DelimitedSplit inline TVF. Use the site search box and type DelimitedSplit.txt to find the thread where I have it uploaded.

create table #TestTab (

Col1 varchar(32)

);

insert into #TestTab

select '\\abc\tri\eds\rf' union all

select '\\fd\tri\ef\' union all

select '\\ghij\tri\gf\rf\k';

select

stuff(

(select '\' + ds.Item from #TestTab t1 cross apply dbo.DelimitedSplit(t1.Col1,'\') ds where ds.ItemID <= 5 and t1.Col1 = t2.Col1 for xml path (''))

,1,1,'')

from

#TestTab t2;

Any one else with a solution, please post it now. Let's see what other options we have for this solution.