Sql Server 2005:Charindex Starting from The End

SQL Server 2005:charindex starting from the end

What do you need to do with it?? Do you need to grab the characters after the last occurence of a given delimiter?

If so: reverse the string and search using the normal CHARINDEX:

declare @test varchar(100)
set @test = 'some.file.name'

declare @reversed varchar(100)
set @reversed = REVERSE(@test)

select 
    REVERSE(SUBSTRING(@reversed, CHARINDEX('.', @reversed)+1, 100))

You'll get back "some.file" - the characters up to the last "." in the original file name.

There's no "LASTCHARINDEX" or anything like that in SQL Server directly. What you might consider doing in SQL Server 2005 and up is great a .NET extension library and deploy it as an assembly into SQL Server - T-SQL is not very strong with string manipulation, whereas .NET really is.

SQL Server 2005 Using CHARINDEX() To split a string

I wouldn't exactly say it is easy or obvious, but with just two hyphens, you can reverse the string and it is not too hard:

with t as (select 'LD-23DSP-1430' as val)
select t.*,
       LEFT(val, charindex('-', val) - 1),
   SUBSTRING(val, charindex('-', val)+1, len(val) - CHARINDEX('-', reverse(val)) - charindex('-', val)),
       REVERSE(LEFT(reverse(val), charindex('-', reverse(val)) - 1))
from t;

Beyond that and you might want to use split() instead.

SQL Server 2005 - RIGHT() not working when adding to CHARINDEX()

I recommend avoiding overly-complex string manipulations by leveraging the power of the CASE statement. Try something like this:

CASE
  when d.target_grade is null then 'NA'
  when d.target_grade = 'N/A' then 'NA'
  when charindex('/', d.target_grade) = 0 then d.target_grade
  else substring(d.target_grade, charindex('/', d.target_grade) + 1, XX)  --  Replace XX with the max posssible length of d.target_grade
END

Find index of last occurrence of a sub-string using T-SQL

You are limited to small list of functions for text data type.

All I can suggest is start with PATINDEX, but work backwards from DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc until you get a result or end up at zero (DATALENGTH-DATALENGTH)

This really is something that SQL Server 2000 simply can't handle.

Edit for other answers : REVERSE is not on the list of functions that can be used with text data in SQL Server 2000

SQL Server 2005: Get first word in a varchar that matches

DECLARE @t TABLE(s VARCHAR(255));

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h';

SELECT RIGHT(LEFT(s, CHARINDEX('°', s)-1), 
  CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX('°', s)-1)))-1) FROM @t;

So, as a computed column:

DECLARE @t TABLE
(
  s VARCHAR(255), 
  x AS CONVERT(VARCHAR(255),RIGHT(LEFT(s, CHARINDEX('°', s)-1), 
    CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX('°', s)-1)))-1)) PERSISTED
);

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h';

SELECT s,x FROM @t;

Results:

Mainly Sunny, 13.7°C                                  13.7
Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h     12

If you might have strings that don't contain a ° symbol, then:

DECLARE @t TABLE
(
  s VARCHAR(255), 
  x AS CONVERT(VARCHAR(255), CASE WHEN CHARINDEX('°', s) > 0 THEN 
    RIGHT(LEFT(s, CHARINDEX('°', s)-1), 
    CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX('°', s)-1)))-1) END) PERSISTED
);

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h'
UNION ALL SELECT 'No weather to report';

SELECT s,x FROM @t;

Results:

Mainly Sunny, 13.7°C                                  13.7
Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h     12
No weather to report                                  NULL

(If you want something else instead of NULL, I can't imagine what, you can add an ELSE to the CASE expression.)

Also, to prove my solution is flexible without introducing a performance-crippling user-defined function:

DECLARE @SearchString VARCHAR(8000);
SET @SearchString = 'km/h'; -- change this to '°'

DECLARE @t TABLE
(
  s VARCHAR(255)
);

INSERT @t SELECT 'Mainly Sunny, 13.7°C'
UNION ALL SELECT 'Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h'
UNION ALL SELECT 'No weather to report'
UNION ALL SELECT 'Wind 102km/h, 23.5°C, mostly cloudy';

SELECT s, x = CONVERT(VARCHAR(255), CASE WHEN CHARINDEX(@SearchString, s) > 0 THEN 
    RIGHT(LEFT(s, CHARINDEX(@SearchString, s)-1), 
    CHARINDEX(' ', REVERSE(LEFT(s, CHARINDEX(@SearchString, s)-1)))-1) END)
FROM @t;

Results:

Mainly Sunny, 13.7°C                                NULL
Partly cloudy, 12°C, Humidity 69%, Wind NE 15km/h   15
No weather to report                                NULL
Wind 102km/h, 23.5°C, mostly cloudy                 102

T-SQL Substring - Last 3 Characters

SELECT RIGHT(column, 3)

That's all you need.

You can also do LEFT() in the same way.

Bear in mind if you are using this in a WHERE clause that the RIGHT() can't use any indexes.

Check if a string contains a substring in SQL Server 2005, using a stored procedure

CHARINDEX() searches for a substring within a larger string, and returns the position of the match, or 0 if no match is found

if CHARINDEX('ME',@mainString) > 0
begin
    --do something
end

Edit or from daniels answer, if you're wanting to find a word (and not subcomponents of words), your CHARINDEX call would look like:

CHARINDEX(' ME ',' ' + REPLACE(REPLACE(@mainString,',',' '),'.',' ') + ' ')

(Add more recursive REPLACE() calls for any other punctuation that may occur)

---

Related Topics