SQL to find row in select

Question

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 column C,

eg. ColumnC value = ABC 123 DEF, and I need the "123" part.

Does anyone know the sql code that I can use for this, in case of a choice, when "123" will always be between two spaces in a line? (MS SQL)

Answer 1

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

If your numbers will be longer than 20-char, 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 ....

Answer 2

CHARINDEX 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

.

Answer 3

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