Need to Delimiter from right to left

Question

Post reply

Need to Delimiter from right to left

sampathsoft

SSC Eights!

Points: 855
More actions
November 22, 2010 at 3:53 am

#72949

Can you change this scenario from right to left .
Thanks
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)
DECLARE @AddString NVARCHAR(50)
--SET @String ='SQL,TUTORIALS'
SET @String ='1.2.3 '
SET @Delimiter = '.'
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @String
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Not sure that I understood what you want. If I understood you correctly then this is the way to do it:

DECLARE @NextString NVARCHAR(40)

DECLARE @pos INT

DECLARE @NextPos INT

DECLARE @String NVARCHAR(40)

DECLARE @Delimiter NVARCHAR(40)

DECLARE @AddString NVARCHAR(50)

--SET @String ='SQL,TUTORIALS'

SET @String ='1.2.3'

SET @Delimiter = '.'

--SET @String = @String + @Delimiter

SET @pos = charindex(@Delimiter,reverse(@String))

WHILE (@pos <> 0)

BEGIN

SET @NextString = left(@String, len(@String)-@pos)

SELECT @String as string

SELECT @NextString as NextString-- Show Results

SET @String = substring(@String,1,len(@String)-@pos)

SET @pos = charindex(@Delimiter,reverse(@String))

END

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sampathsoft SSC Eights! Points: 855 More actions · Answer 2

Thanks .. I needed this output..

Thanks again for your quick Reply

Are there any place to Mark this thread as Answered post

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Adi's solution fails for strings with trailing spaces.

Try it for @String ='1.2.3 '

To make it work replace "len(@String)" with "DATALENGTH(@String)/2"

Use "/2" only if @String is Nvarchar value, if it's just varchar use only "DATALENGTH(@String)"

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Adi Cohn-120898 (11/22/2010)
Not sure that I understood what you want. If I understood you correctly then this is the way to do it:
DECLARE @NextString NVARCHAR(40)
DECLARE @pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)
DECLARE @AddString NVARCHAR(50)
--SET @String ='SQL,TUTORIALS'
SET @String ='1.2.3'
SET @Delimiter = '.'
--SET @String = @String + @Delimiter
SET @pos = charindex(@Delimiter,reverse(@String))
WHILE (@pos <> 0)
BEGIN
SET @NextString = left(@String, len(@String)-@pos)
SELECT @String as string
SELECT @NextString as NextString-- Show Results
SET @String = substring(@String,1,len(@String)-@pos)
SET @pos = charindex(@Delimiter,reverse(@String))
END
Adi

I have to ask, Adi... why are you using a loop for this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

sampathsoft (11/22/2010)
Can you change this scenario from right to left .
Thanks
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)
DECLARE @AddString NVARCHAR(50)
--SET @String ='SQL,TUTORIALS'
SET @String ='1.2.3 '
SET @Delimiter = '.'
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @String
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

I'd like to show you a high speed method for doing this but it's unclear from yours and Adi's output as to what you really want... Do you want...

1.2.3

1.2

1

... or do you want ...

3

2

1

Let me know and I'll show you a trick or two because the use of REVERSE (especially in a While Loop) is very costly performance wise and resource wise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)