CODEWITHSUNDEEP
sqlsql-server
ferryh
ferryh

Reputation: 13

Remove all characters not like desired value

I'm using SQL server 2012, and I have an issue with certain values. I want to extract a specific set of values from a string (which is in the entire column) and want to just retrieve the specific value.

The value is: SS44\\230433\586 and in other value it's 230084android, and the third orderno 239578

The common denominator is that all numbers start with 23, and are 6 characters long. All other values have to be removed from the string. I tried rtrim and a ltrim but that didn't give me the desired output.

I'm not sure as to how to do this without regex.

Upvotes: 1

Views: 57

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

You can use PATINDEX to find the start of the number and SUBSTRING to get the next 6 digits:

declare @Value varchar(50) = 'SS44\\230433\586'
select substring(@Value, patindex('%23%', @Value), 6)

If you want to be a bit more careful with the searching, you can use PATINDEX and check next 4 symbols - are they digits:

patindex('%23[0-9][0-9][0-9][0-9]%', @Value)

Eventually, you can store the result returned and check is there a match:

declare @Value varchar(50) = 'SS44\\230433\586'
declare @StartIndex int
set @StartIndex = patindex('%23[0-9][0-9][0-9][0-9]%', @Value)
select IIF(@StartIndex > 0, substring(@Value, @StartIndex, 6), null)

Upvotes: 2

Related Questions