Getting first n numeric characters from a varchar column in SQL

Question

I'm building a hash, using the house number from an address field. What SQL can I use to select only the numeric characters from the front of a varchar field?

EX:

12345 South 1st Street

I want the 12345. Thanks in advance.

Answer 1

declare @MyColumn as varchar(250)
set @MyColumn = '12345 South 1st Street'
select @MyColumn, cast(left(@MyColumn, patindex('%[^0-9]%', @MyColumn)) as int)

If you do not know that there will be a number for sure, add a CASE statement as follows to prevent errors:

declare @MyColumn as varchar(250)
set @MyColumn = 'South 1st Street'
select @MyColumn as Address, case when patindex('%[^0-9]%', @MyColumn) = 1 then '' else left(@MyColumn, patindex('%[^0-9]%', @MyColumn)) end as StreetNumber

If there could be leading spaces, you may also want to add an LTRIM:

declare @MyColumn as varchar(250)
set @MyColumn = ' 12345 South 1st Street'
select @MyColumn as Address, case when patindex('%[^0-9]%', ltrim(@MyColumn)) = 1 then '' else left(ltrim(@MyColumn), patindex('%[^0-9]%', ltrim(@MyColumn))) end as StreetNumber

Answer 2

Do you assume that there will be a space after the numeric characters, and that there will always be numeric characters?

DECLARE @addr VARCHAR(100)
SET @addr='12345 South 1st St'
SELECT SUBSTRING(@addr,0,CHARINDEX(' ',@addr))

Answer 3

DECLARE @TestVal VARCHAR(100)
SET @TestVal = '12345 South 1st Street'

SELECT 
    CASE 
        WHEN @TestVal LIKE '% %' THEN LEFT(@TestVal , CHARINDEX(' ', @TestVal ) - 1) 
        ELSE @TestVal 
    END 

This will also work if the value doesn't contain a space, or is null.

If you want EXACTLY like you asked for (only the numeric characters from the front of the string), then try this. For example 'MyHouseName, SomeStreet' would return '' as it doesn't have a house number. '12A Flat, SomeStreet' would return '12'. This doesn't sound ideal, but as you specifically said "numeric" characters, I've expanded to this alternative:

DECLARE @TestVal VARCHAR(100)
SET @TestVal = '12345 South 1st Street'

SELECT CASE 
    WHEN @TestVal LIKE '[0-9]%' THEN    
            CASE 
                WHEN @TestVal LIKE '%[^0-9]%' THEN LEFT(@TestVal, PATINDEX('%[^0-9]%', @TestVal) - 1) 
                ELSE @TestVal 
            END
    ELSE '' END

Answer 4

SELECT LEFT(address, INSTR(address, ' ') - 1) FROM address_table

It finds the position of the first space and grabs the text left of it.