CODEWITHSUNDEEP
sql-server-2012substring
Chad Portman
Chad Portman

Reputation: 1216

SQL Select between two spaces or everything after first if only one

I have a column that contains the description of a group each row looks something like on of the two options below with a varying number of words and or numbers in place of blah:

bla1h bla2h blah blah Group 18956
blah bl4ah blah Group 189 bla5h

I am needed to extract the numbers after the word group.

I was able to come up with this code:

SUBSTRING(R.GroupDescription,CHARINDEX('Group ',R.GroupDescription),20)

This will trim it one of the below:

Group 18956
Group 189 blah

After this I need to trim it further so I was able to come up with the below code thanks to This post:

LEFT(SUBSTRING(SUBSTRING(R.GroupDescription,CHARINDEX('Group ',R.GroupDescription),20),CHARINDEX(' ',SUBSTRING(R.GroupDescription,CHARINDEX('Group ',R.GroupDescription),20)) +1,LEN(SUBSTRING(R.GroupDescription,CHARINDEX('Group ',R.GroupDescription),20))),CHARINDEX(' ',SUBSTRING(SUBSTRING(R.GroupDescription,CHARINDEX('Group ',R.GroupDescription),20), CHARINDEX(' ', SUBSTRING(R.GroupDescription,CHARINDEX('Group ',R.GroupDescription),20)) +2, LEN(SUBSTRING(R.GroupDescription,CHARINDEX('Group ',R.GroupDescription),20)))))

This code is both cumbersome and only works on the second example from above in the first example where there is nothing after the set of numbers I want It returns nothing.

Also the real fun part of this is the returned result is going to be needed to do a join to another table.

I am using SSMS 2012 with a SQL Server 2012 back end.

Upvotes: 2

Views: 967

Answers (2)

Alex K.
Alex K.

Reputation: 175766

One way assuming there is a single Group always followed by a number:

;WITH R(GroupDescription) AS (
    SELECT 'blah blah blah blah Group 18956' UNION
    SELECT 'blah blah blah Group 189 blah' 
)
SELECT LEFT(T.F, CHARINDEX(' ', T.F + ' ') - 1) VAL
FROM (SELECT
    SUBSTRING(R.GroupDescription, CHARINDEX('Group ',R.GroupDescription) + 6, LEN(R.GroupDescription)) F
    FROM R) T

VAL
18956
189

Upvotes: 2

dfundako
dfundako

Reputation: 8314

It sounds like you would benefit from a function that will strip all non 0-9 chars from your long string. This would leave only the numbers and then you can join on that to your tables. 

CREATE FUNCTION strip_function (@value VARCHAR(200)) 
RETURNS VARCHAR(200)
AS 
BEGIN

WHILE PATINDEX('%[^0-9]%', @value) > 0 
SET @value = REPLACE(@value, SUBSTRING(@value, PATINDEX('%[^0-9]%', @value),1),'')

RETURN @value 
END 
GO 

SELECT dbo.strip_function(your_column)
FROM your_table

Upvotes: 0

Related Questions