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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-09-25 : 01:47:15
|
| Bosstan writes "How to locate last occurrence of character in string?" |
|
|
Kristen
Test
22859 Posts |
Posted - 2008-06-18 : 06:51:37
|
| Just been looking for a function that will return the reminder of a string AFTER the final match with a substring.I was trying to avoid using the same CHARINDEX twice, for performance reasons, and came up with this:SELECT COALESCE(RIGHT(@search, NullIf(CHARINDEX(REVERSE(@Find), REVERSE(@search)), 0)-1), @search)Just in case it is useful to anyone else |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-18 : 07:44:57
|
Hi Kristen!When you decided to come back to SQLTeam, you decided to revive a 7 year old topic? 
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2008-06-18 : 08:27:01
|
| This was the only thread I found that matched what I was looking for. I figured that other folk may find this thread if they had a similar requirement. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-18 : 09:04:10
|
quote:
Originally posted by Kristen
Just been looking for a function that will return the reminder of a string AFTER the final match with a substring.I was trying to avoid using the same CHARINDEX twice, for performance reasons, and came up with this:SELECT COALESCE(RIGHT(@search, NullIf(CHARINDEX(REVERSE(@Find), REVERSE(@search)), 0)-1), @search)Just in case it is useful to anyone else
Welcome back Kristen I dont understand the outputdeclare @search varchar(1000), @find char(1)set @search='this is tested by test'SELECT COALESCE(RIGHT(@search, NullIf(CHARINDEX(REVERSE(@Find), REVERSE(@search)), 0)-1), @search)MadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-18 : 09:28:58
|
If you don't know what you search for (@find is null) you will get all original string.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-18 : 09:33:10
|
quote:
Originally posted by Peso
If you don't know what you search for (@find is null) you will get all original string.
E 12°55'05.25"N 56°04'39.16"
Thanks Peso. I missed to note that MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2008-06-19 : 03:34:17
|
| Hmmm ... I didn't have @FIND=NULL in my test set.I wonder if @FIND=NULL should return @SEARCH? I think returning NULL would be better in that case.I suppose the minimalist way of doing that would be:SELECT CASE WHEN @Find IS NULL THEN NULL ELSE COALESCE(RIGHT(@search, NullIf(CHARINDEX(REVERSE(@Find), REVERSE(@search)), 0)-1), @search) END |
|
|
|
|
|
|
|
|
How to locate last occurrence of character in string?