Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
barnabeck
Posting Yak Master
236 Posts |
Posted - 2013-04-04 : 08:28:03
|
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 T1TC3-T2 T2TC4-T2 T2TT TTTC 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-04 : 10:29:21
|
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 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-04-04 : 10:30:51
|
or this: case when charindex('-',column) = 0 then column else right(column, charindex('-', reverse(column))-1) end Be One with the OptimizerTG |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-04-04 : 10:56:55
|
or without the case when checkingright(col, charindex('-', reverse(col) + '-') - 1) KH[spoiler]Time is always against us[/spoiler] |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-04-07 : 12:54:29
|
quote: Originally posted by khtan or without the case when checkingright(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." |
|
|
|
|
|
|
|