Substring Functions

Question

Need to pull the numeric values from the columns Routing number and Account_Number

The values in the Routing_number column:

031100225  TOTAL FRAUD,
101190178  NON CASH,
071000288 NOT ON US,
101104407  10 DAY SENT,
065000090    REQ 10 DAY 81615,
101101141, DATED 032409,
101100045, FOR A FORD TAURUS

I need to select Routing number as - 031100225,101190178,071000288

The values in the Account_number column:

2079950059473 TOTAL FRAUD,
000480152617 KC MASTER COMP,
2538867, STALE DATE, CHECK
7003475514   FROZEN BLOCKED,
5049530455, NSF CHECK

I need to select account number as 2079950059473,000480152617

I need a SUBSTRING function to select only the numeric part within the column.

Thanks

Answer 1

Use PATINDEX

SELECT LEFT('031100225  TOTAL FRAUD',PATINDEX('%[^0-9]%','031100225  TOTAL FRAUD') - 1)

SELECT LEFT('2079950059473 TOTAL FRAUD,',PATINDEX('%[^0-9]%','2079950059473 TOTAL FRAUD,') - 1)

Etc...

SELECT 
    LEFT(Routing_number,PATINDEX('%[^0-9]%',Routing_number) - 1) as ROUT,
    LEFT(Account_number,PATINDEX('%[^0-9]%',Account_number) - 1) as ACCT
FROM 
    YourTable

EDIT

SELECT
        CASE
            WHEN Routing_number IS NULL THEN NULL
            WHEN TRY_CONVERT(INT,Routing_number) IS NULL THEN LEFT(Routing_number,PATINDEX('%[^0-9]%',Routing_number) - 1)
            ELSE Routing_number 
        END AS ROUT,
        CASE
            WHEN Account_number IS NULL THEN NULL
            WHEN TRY_CONVERT(INT,Account_number) IS NULL THEN LEFT(Account_number,PATINDEX('%[^0-9]%',Account_number) - 1)
            ELSE  Account_number
        END AS ACCT
    FROM 
        YourTable

Using ISNUMERIC instead of TRY_CONVERT

SELECT
        CASE
            WHEN Routing_number IS NULL THEN NULL
            WHEN ISNUMERIC(Routing_number) = 0 THEN LEFT(Routing_number,PATINDEX('%[^0-9]%',Routing_number) - 1)
            ELSE Routing_number 
        END AS ROUT,
        CASE
            WHEN Account_number IS NULL THEN NULL
            WHEN ISNUMERIC(Account_number) = 0 THEN LEFT(Account_number,PATINDEX('%[^0-9]%',Account_number) - 1)
            ELSE  Account_number
        END AS ACCT
    FROM 
        YourTable

Answer 2

I've created a function to perform this operation in a database I use, keeps the query syntax more straight forward:

CREATE FUNCTION [dbo].[RemoveNonNumericCharacters](@result VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN

    DECLARE @Numbers AS VARCHAR(50) = '%[^0-9]%'
    WHILE PatIndex(@Numbers , @result) > 0
        SET @Temp = Stuff(@result, PatIndex(@Numbers , @result), 1, '')

    RETURN @result
END

Sample:

SELECT dbo.RemoveNonNumericCharacters('20sdsdsd sd sdsd3920keosd e20e920e')

Returns:

20392020920

To use in a SELECT:

SELECT dbo.RemoveNonNumericCharacters(Routing_number) AS NewValue
FROM [YOUR_TABLE]

Answer 3

scsimon's nicely designed PATINDEX solution is the way to go but it will throw that Invalid length parameter passed to the LEFT or SUBSTRING function error you mentioned if you pass an empty string or a record which only contains numbers. Below is a similar solution that handles purely numeric values and empty strings:

DECLARE @yourTable TABLE (Routing_Number varchar(100));
INSERT @yourTable VALUES 
('031100225 TOTAL FRAUD'),('101190178 NON CASH'),('071000288'),('');

-- This will fail when there's only numbers or the string is empty:
/*
SELECT 
  SUBSTRING
  (
    Routing_Number, 1,
    PATINDEX('%[^0-9]%',Routing_number)-1
  ) AS ROUT
FROM @yourTable;
*/

SELECT 
  SUBSTRING
  (
    Routing_Number, 1,
    ISNULL(NULLIF(PATINDEX('%[^0-9]%',Routing_number)-1,-1),8000)
  ) AS ROUT
FROM @yourTable;