CODEWITHSUNDEEP
sqlsql-servert-sqlcase
Grace
Grace

Reputation: 2588

SQL to find string in select

I have a select statement like this:

SELECT ColumnA,
CASE ColumnB = 'England' THEN ...

In the part after the THEN statement, i want to take the numbers from ColumnC,

e.g. ColumnC value = ABC 123 DEF, and i need the '123' part.

Does anyone know the sql code i can use to do this within the select when the '123' will always be in between the only 2 spaces in the string? (MS SQL)

Upvotes: 3

Views: 11346

Answers (3)

cjk
cjk

Reputation: 46425

You can use a combination of CHARINDEX and SUBSTRING:

DECLARE @Test TABLE(ColumnC varchar(100))
INSERT @Test 
VALUES ('ABC 123 DEF')

SELECT SUBSTRING(ColumnC,
          CHARINDEX(' ', ColumnC) + 1, -- first space
          CHARINDEX(' ', ColumnC, CHARINDEX(' ', ColumnC) + 1)
             - CHARINDEX(' ', ColumnC)) -- length from first to second space 
FROM @Test

This works as expected for the sample string provided.

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

The main key is that you need to use ColumnC LIKE '% % %' so that it does not fail when the data does not contain two spaces.

If your numbers are going to be less than 20-char long, you can use this

SELECT ColumnA,
CASE WHEN ColumnB = 'England' AND ColumnC LIKE '% % %' THEN
   RTRIM(LEFT(REPLACE(STUFF(columnc, 1, PatIndex('% %', columnc), ''), ' ', REPLICATE(' ', 20)),20))
ELSE ....

Or you can use this

SELECT ColumnA,
CASE WHEN ColumnB = 'England' AND ColumnC LIKE '% % %' THEN
    SUBSTRING(
        SUBSTRING(
            ColumnC,
            1,
            CHARINDEX(' ',ColumnC,CHARINDEX(' ', ColumnC)+1)-1),
        1+CHARINDEX(' ', ColumnC),
        LEN(ColumnC))
ELSE ....

Upvotes: 3

rwired
rwired

Reputation: 1133

SUBSTRING_INDEX(SUBSTRING_INDEX( ColumnC , ' ', 2 ),' ',-1)

Upvotes: 0

Related Questions