I really need to start using Stuff more - I have been one-upped twice in a week using stuff over substring. 😀

Thanks, I am going to really try and...

I am sure there are other ways as well, but here is one way:

DECLARE @tbl TABLE(field1 VARCHAR(5), field2 VARCHAR(5), field3 VARCHAR(5))

INSERT INTO @tbl (field1, field2, field3) VALUES ('one', 'two', 'three')

INSERT...

Oops, I missed that you wanted all the values of B.Col2 on one line. This does not get you that. Removed code. Sorry

Please provide some insert ready test data and expected results for tested code.

[/quote]

Heh.... I understand the lesson in progress but I thought the requirements were actually pretty clear on this one. From the OP...

[/quote]

Yeah - when looking at just this post,...

This is why we are looking for requirements.

DECLARE @STR NVARCHAR(30)

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

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

I am posting my solution.

Good question, a follow up question is do you just want to get rid of everything past...

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...

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...

Oh good, I was able to save face for that substring inefficient code. 😀

SELECT ISNULL(STUFF(@str, CHARINDEX('.', @STR), CHARINDEX('\', @STR, 3) - CHARINDEX('.', @STR) - 1, ''), @STR);

Thanks! I have used stuff once or twice - you are correct - underused!

DECLARE @STR NVARCHAR(30)

SET @STR = '\\xyz.ed.kl.e.a\fgd\'

SELECT SUBSTRING(@str, 1, CHARINDEX('.', @STR) - 1) + SUBSTRING(@str, CHARINDEX('\', @STR, 3), 4)

...

My query will do that. you want col1 to be the same as col2.