CODEWITHSUNDEEP
sqlsql-servert-sql
M_Jero
M_Jero

Reputation: 49

SQL split only the numeric values from a varchar

I have a column with the following values:

Column A
------------
001 TestA
002 TestB
003 TestC

Now I want only the numeric values from the Column A like:

001
002
003

Please suggest a solution in SQL

Upvotes: 0

Views: 1733

Answers (3)

Alan Burstein
Alan Burstein

Reputation: 7918

Using some more troublesome sample data:

IF OBJECT_ID('#myTable') IS NOT NULL DROP TABLE #myTable;
CREATE TABLE #myTable (colA varchar(100));
INSERT #myTable 
VALUES ('001 TestA'),('0002 TestB'),('00003 TestC'),('004TestD!!!'),
('5500599' ),(' 06 TestF'), ('XXX 7 TestG'), ('eight TestH'),(NULL);

This would still be simple using DigitsOnlyEE. 

SELECT * 
FROM #myTable
CROSS APPLY dbo.DigitsOnlyEE(colA);

results:

colA            DigitsOnly
--------------- ----------
001 TestA       001
0002 TestB      0002
00003 TestC     00003
004TestD!!!     004
5500599         5500599
 06 TestF       06
XXX 7 TestG     7
eight TestH     NULL
NULL            NULL

Alternatively you could do this:

WITH myTableCTE AS 
(
  SELECT colA, new = SUBSTRING(colA,NULLIF(PATINDEX('%[0-9]%',colA),0),100)
  FROM #myTable
)
SELECT colA, colA_new = SUBSTRING(new,1,COALESCE(NULLIF(PATINDEX('%[^0-9]%',new),0)-1,100))
FROM myTableCTE;

For fun, let's say your column could have multiple numbers like this:

IF OBJECT_ID('#myTable') IS NOT NULL DROP TABLE #myTable;
CREATE TABLE #myTable (colA varchar(100));
INSERT #myTable 
VALUES ('001 TestA 555'),('0002 TestB 123'),('03 TestC 555'),('[1234][22345][335][44]...');

you could use PatternSplitCM like this:

SELECT colA, item 
FROM #myTable
CROSS APPLY dbo.patternSplitCM(colA, '%[0-9]%')
WHERE [matched] = 1;

results:

colA                       item
-------------------------- -------
001 TestA 555              001
001 TestA 555              555
0002 TestB 123             0002
0002 TestB 123             123
03 TestC 555               03
03 TestC 555               555
[1234][22345][335][44]...  1234
[1234][22345][335][44]...  22345
[1234][22345][335][44]...  335
[1234][22345][335][44]...  44

... and, because I'm bored, we'll use PatternSplitCM to pull out possible currency.

IF OBJECT_ID('#myTable') IS NOT NULL DROP TABLE #myTable;
CREATE TABLE #myTable (colA varchar(100));
INSERT #myTable VALUES ('$100,000.00 TestA $5.44'),('$66.22 TestB 12.3456 xxx 333.00');

SELECT colA, item 
FROM #myTable
CROSS APPLY dbo.patternSplitCM(colA, '%[0-9.,$]%')
WHERE [matched] = 1;

results:

colA                              item
--------------------------------- -----------
$100,000.00 TestA $5.44           $100,000.00
$100,000.00 TestA $5.44           $5.44
$66.22 TestB 12.3456 xxx 333.00   $66.22
$66.22 TestB 12.3456 xxx 333.00   12.3456
$66.22 TestB 12.3456 xxx 333.00   333.00

Upvotes: 0

shambhu yadav
shambhu yadav

Reputation: 241

  1. If you are sure, there will always be 3 digits only then you can go with:

    Select LEFT([Column A],3)

  2. If numeric digit length is not fixed, but, you will get space between number and digits. In this case you can use:

    Select LEFT([Column A], CHARINDEX( ' ', [Column A]))

  3. If you want it universal and you are sure you will not value in decimal (Like 123.23). Then, you can go with following;

    Select LEFT([Column A], PATINDEX('%[^0-9]%', [Column A]) - 1)

NOTE: Reply Me if you face any issue in this or out of this.

Upvotes: 3

Dai
Dai

Reputation: 155065

If you can guarantee that there will always be a space character between the numbers and the text then use CHARINDEX with SUBSTRING:

SELECT
    SUBSTRING( [Column A], 1, CHARINDEX( ' ', [Column A] ) ) AS Digits
FROM
    myTable

Upvotes: 1

Related Questions