Get the Numbers before the first letter in SQL Server

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 233
Let's say we have a table of records as under

Record
------
01A1
02A1
18A1
20A1
17A1
19A1
01B1
02B1
20B1
17B1
18B1
19B1


We need the extract the Numbers before the first letter such that the final output should appear as

Record	Numbers
----- -------
01A1 01
01B1 01
02B1 02
02A1 02
17A1 17
17B1 17
18B1 18
18A1 18
19A1 19
19B1 19
20B1 20
20A1 20


The below query will do so

DECLARE @T TABLE(Record VARCHAR(20))

INSERT INTO @T

SELECT '01A1' UNION ALL
SELECT '02A1' UNION ALL
SELECT '18A1' UNION ALL
SELECT '20A1' UNION ALL
SELECT '17A1' UNION ALL
SELECT '19A1' UNION ALL
SELECT '01B1' UNION ALL
SELECT '02B1' UNION ALL
SELECT '20B1' UNION ALL
SELECT '17B1' UNION ALL
SELECT '18B1' UNION ALL
SELECT '19B1'

SELECT
Record
,Numbers = REVERSE(SUBSTRING(REVERSE(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 2)),2,LEN(REVERSE(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 2)))))
FROM @T
ORDER BY
CAST(REVERSE(SUBSTRING(REVERSE(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 2)),2,LEN(REVERSE(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 2))))) AS INT)

Comments or Responses

Login to post response