SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Right Crop of a String to 1st occurance from right
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
194 Posts

Posted - 04/04/2013 :  08:28:03  Show Profile  Reply with Quote
I have values with hyphen (or not) and I want to be returned only the part on the right side of the hyphen (in case there is any).
Example, returned values in red:

TD3-T1     T1
TC3-T2     T2
TC4-T2     T2
TT         TT
TC         TT
I do:
RIGHT (COLUMN, LEN(COLUMN)-PATINDEX ('%-%',COLUMN))
which works, but I thought there must be a smarter and faster way. Beside: as soon as there are 2 hyphens my algorithm fails as it gets only the first occurance.

Any comment?
Martin

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/04/2013 :  10:29:21  Show Profile  Reply with Quote
The code below is neither faster nor smarter, but it takes care of multiple hyphens:
CASE 
	WHEN COLUMN like '%-%' THEN REVERSE(STUFF(REVERSE(COLUMN),patindex('%-%',reverse(COLUMN)),len(COLUMN),''))
	ELSE COLUMN 
END
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/04/2013 :  10:30:51  Show Profile  Reply with Quote
or this:

  case 
   when charindex('-',column) = 0 then column
   else right(column, charindex('-', reverse(column))-1)
  end


Be One with the Optimizer
TG
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 04/04/2013 :  10:56:55  Show Profile  Reply with Quote
or without the case when checking
right(col, charindex('-', reverse(col) + '-') - 1)



KH
Time is always against us

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 04/07/2013 :  12:54:29  Show Profile  Reply with Quote
quote:
Originally posted by khtan

or without the case when checking
right(col, charindex('-', reverse(col) + '-') - 1)



It would also appear to be pretty good in the area of performance as well as the shortest. Here's some test data for folks to play with.
WITH cteBaseData([Column]) AS
        (
         SELECT 'TD3-T1' UNION ALL
         SELECT 'TC3-T2' UNION ALL
         SELECT 'TC4-T2' UNION ALL
         SELECT 'TT' UNION ALL
         SELECT 'TC' UNION ALL
         SELECT 'TD3-T1-T2-T3'
        )
 SELECT TOP 100000
        SomeID = IDENTITY(INT,1,1),
        data.[Column]
   INTO #TestTable
   FROM cteBaseData data
  CROSS JOIN master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;


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

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

Edited by - Jeff Moden on 04/07/2013 13:00:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000