Remove Characters

Question

Remove Characters

bpowers

SSCertifiable

Points: 5483
More actions
November 11, 2014 at 10:29 am

#295002

What would be the best approach to just return the characters after the last special character (-) in a string?
Example:
ReportServer-SalesOrders
ReportServer-OKLA-SalesOrders
I only want to return "SalesOrders". Any help will be appreciated.
Thanks

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

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

djj SSCoach Points: 18831 More actions · Answer 1

bpowers (11/11/2014)
What would be the best approach to just return the characters after the last special character (-) in a string?
Example:
ReportServer-SalesOrders
ReportServer-OKLA-SalesOrders
I only want to return "SalesOrders". Any help will be appreciated.
Thanks

This is most likely not the "best"

DECLARE @x table (y varchar(100));

INSERT INTO @x VALUES

('ReportServer-SalesOrders'),

('ReportServer-OKLA-SalesOrders');

SELECT REVERSE(left(REVERSE(Y), charindex('-', REVERSE(y))-1)) FROM @x;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Here are 2 additional options:

Note that PARSENAME can return incorrect results if you have dots in your values or if you have more than 4 sections.

DECLARE @x table (y varchar(100));

INSERT INTO @x VALUES

('ReportServer-SalesOrders'),

('ReportServer-OKLA-SalesOrders');

SELECT REVERSE(left(REVERSE(Y), charindex('-', REVERSE(y))-1)) ,

RIGHT(Y, charindex('-', REVERSE(y))-1),

PARSENAME( REPLACE( y, '-', '.'),1)

FROM @x;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

bpowers SSCertifiable Points: 5483 More actions · Answer 3

bpowers

SSCertifiable

Points: 5483

November 11, 2014 at 11:06 am

#1757761

Thanks for the feedback. Works like a charm!

mickyT SSChampion Points: 10360 More actions · Answer 4

Another thing, all the options so far (except the parsename) will fail if there isn't a '-'.

My preference is generally to use STUFF, but it's much the same as the others.

WITH sample AS (

SELECT *

FROM (VALUES('something-result'),('noresult'),('need-this-result'))s(Val)

)

SELECT NULLIF(

STUFF(Val,1,len(Val)-Charindex('-',reverse(Val))+1,'')

,'')

FROM sample

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Why do I always forget the safety net? I live on the edge. :hehe:

DECLARE @x table (y varchar(100));

INSERT INTO @x VALUES

('ReportServer-SalesOrders'),

('ReportServer-OKLA-SalesOrders'),

('SalesOrders');

SELECT REVERSE(left(REVERSE(Y), charindex('-', REVERSE(y) + '-')-1)) ,

RIGHT(Y, charindex('-', REVERSE(y) + '-')-1),

PARSENAME( REPLACE( y, '-', '.'),1)

FROM @x;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mickyT SSChampion Points: 10360 More actions · Answer 6

Luis Cazares (11/11/2014)
Why do I always forget the safety net? I live on the edge. :hehe:

I usually do as well, right up until the fall 😀