November 11, 2014 at 10:29 am
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
November 11, 2014 at 10:37 am
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;
November 11, 2014 at 11:01 am
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;
November 11, 2014 at 11:06 am
Thanks for the feedback. Works like a charm!
November 11, 2014 at 11:25 am
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
November 11, 2014 at 11:30 am
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;
November 11, 2014 at 11:34 am
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 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply