Right Crop of a String to 1st occurance from right

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Right Crop of a String to 1st occurance from right

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     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
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 Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-04-04 : 10:56:55

or without the case when checking

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources