Split a field with duplicate seperators

Question

Post reply

Split a field with duplicate seperators

LoosinMaMind

Hall of Fame

Points: 3366
More actions
June 20, 2011 at 10:08 am

#389784

Hi, I have the following data in a field.
RNL00:123456-3:1
i need to extract the last character following the second ':', Problem is there are 2 in the field.
This may not always be a single character it could be up to 3. For example
RNL00:123456-3:123
I am trying to get this into a view from a table so i can filter on any '1's
Any sugestions would be greatly recieved.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Revenant SSC-Forever Points: 42467 More actions · Answer 1

REVERSE your string, split the first portion and REVERSE it back.

kramaswamy SSCoach Points: 18175 More actions · Answer 2

Are you asking for the character immediately following the second ":"? Or are you asking for the last character following the second ":"? And if it is the latter, then is there a possibility of having more than two ":" in the string?

Character immediately following the second ":", assuming only two ":" in string:

DECLARE @String VARCHAR(MAX)

SET @String = 'RNL00:123456-3:123'

PRINT SUBSTRING(@String, LEN(@String) - CHARINDEX(':', REVERSE(@String)) + 2, 1)

Last character following the second ":", assuming only two ":" in string:

DECLARE @String VARCHAR(MAX)

SET @String = 'RNL00:123456-3:123'

PRINT SUBSTRING(@String, LEN(@String), 1)

Character immediately following the second ":", if there are more than two ":" in string:

DECLARE @String VARCHAR(MAX)

SET @String = 'RNL00:123456-3:123'

PRINT SUBSTRING(SUBSTRING(@String, CHARINDEX(':', @String) + 1, LEN(@String)), CHARINDEX(':', SUBSTRING(@String, CHARINDEX(':', @String) + 1, LEN(@String))) + 1, 1)

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 3

Thanks for your reply.

I'm after all characters after the second ':'

There may be 1 or 2 characters max. There will onl y ever be 2 ':' in the field

So where field is RNL00:1234567-1:9 i would expect to see '9'

but

where field is RNL00:1234567-1:12 i would expect to see '12'

Thanks in advance

kramaswamy SSCoach Points: 18175 More actions · Answer 4

In that case, this should do it:

DECLARE @String VARCHAR(MAX)

SET @String = 'RNL00:123456-3:123'

PRINT SUBSTRING(@String, LEN(@String) - CHARINDEX(':', REVERSE(@String)) + 2, LEN(@String))

Or, if there can be more than two ":" in string,

DECLARE @String VARCHAR(MAX)

SET @String = 'RNL00:123456-3:123'

PRINT SUBSTRING(SUBSTRING(@String, CHARINDEX(':', @String) + 1, LEN(@String)), CHARINDEX(':', SUBSTRING(@String, CHARINDEX(':', @String) + 1, LEN(@String))) + 1, LEN(@String))

Revenant SSC-Forever Points: 42467 More actions · Answer 5

This is my take on it:

DECLARE @string VARCHAR(MAX) = 'RNL00:123456-3:123';

DECLARE @nibble VARCHAR(MAX) = REVERSE(@string);

SET @nibble = REVERSE( SUBSTRING( @nibble, 0, CHARINDEX( ':', @nibble ) ) );

If you wanted to make it really professional, you would check for presence of ':' and if it is not found, return either the entire string or an empty string, depending on the requirements.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Will dot ( . ) ever naturally appear in the string?

If not then something like this may work:

SELECT PARSENAME(REPLACE('RNL00:123456-3:1', ':', '.'), 1),

PARSENAME(REPLACE('RNL00:123456-3:123', ':', '.'), 1) ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

kramaswamy SSCoach Points: 18175 More actions · Answer 7

Haha, wow, that is one cute solution. Faking the system into thinking the string is referencing a database table, and then using PARSENAME to display the name of the "table". Nice 😛

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

kramaswamy (6/20/2011)
Haha, wow, that is one cute solution. Faking the system into thinking the string is referencing a database table, and then using PARSENAME to display the name of the "table". Nice 😛

It's neat 🙂 The first time I saw it used this way was on these site...I wish I had saved the thread so I could give proper credit.

It works great for these types of artificial multi-part keys conjured by a system. That's why I asked about whether dot was possible, because most times you can answer that definitively for them and know whether PARSENAME is a viable option.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Revenant SSC-Forever Points: 42467 More actions · Answer 9

Both thumbs up!

As they say, you learn the important stuff only if you keep on learning after you think you know it all...

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 10

Many Thanks fo all your advice. I have my query working a treat now.

Thanks Again