How to find 5 digit number in a varchar?

Question

Basically I just need to get a 5 digits number that is separated by a space. The 5 digits number can be anywhere in the varchar.

Example: I have a varchar column with this various data in SQL 2008 table

travel visa 34322 LLL001
Coffee 34332 Jakarta 
FDR001 34312 Taxi cost cash
taxi cash 34321
34556 eating dinner with customer
eating dinner 34256 with customer
visa cost 34221 REF773716637366

the 5 digits number can be anywhere separated by a space what is best to extract this?

Thanks

Row like this should return blank

Visa refNbr 778738878

Tried the following with no luck yet

SELECT  pjtran.tr_comment 
,substring(pjtran.tr_comment,PATINDEX('%[0-9]%',pjtran.tr_comment),5)
,Left(SubString(pjtran.tr_comment, PatIndex('%[0-9.-]%', pjtran.tr_comment), 50),PatIndex('%[^0-9.-]%', SubString(pjtran.tr_comment, PatIndex('%[0-9.-]%', pjtran.tr_comment), 50) + 'X')-1)
,len(pjtran.tr_comment)-len(replace(pjtran.tr_comment,' ',''))

I think I need to use a combination of counting the number of space in the varchar. and the above. but I am not sure how to do it

Is there ever a situation where there are multiple numbers in a string? — Elias, Jun 16 '14 at 19:23
Check out the function at this link http://blogs.lessthandot.com/index.php/datamgmt/datadesign/extracting-numbers-with-sql-server/ — Elias, Jun 16 '14 at 19:25
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/ using a udf. — xQbert, Jun 16 '14 at 19:27
Possible duplicate question: http://stackoverflow.com/questions/375133/how-do-i-extract-part-of-a-string-in-t-sql — Dusan, Jun 16 '14 at 19:31
Basically I just need to get a 5 digits number that is separated by a space. If there is a duplicate 5 digits number in the varchar, I just get the first 5 digits. eg: "taxi price 34523 33564" then I just get 34523. thanks — BobNoobGuy, Jun 16 '14 at 19:38

score 4 · Accepted Answer · answered Jun 16 '14 at 19:29

How about something like this?

 select substring(tr_comment, patindex('%[0-9][0-9][0-9][0-9][0-9] %', tr_comment), 5) as zip5

If you want to consider that it might be at the end of the string:

 select substring(tr_comment, patindex('%[0-9][0-9][0-9][0-9][0-9] %', tr_comment + ' '), 5
                 ) as zip5

score 0 · Answer 2 · answered Jun 16 '14 at 19:31

0

use this, this may help you

 select SUBSTRING(tr_comment, PATINDEX('%[0-9]%', tr_comment), 5) as zip

answered Jun 16 '14 at 19:31

ashok_p

741
3
8
17

score 0 · Answer 3 · answered Feb 28 '18 at 17:02

Please Try It

CREATE FUNCTION [dbo].[udf_ExtractNumberFromString]
(
    @pInputString VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE @OutputString varchar(MAX)=''
    DECLARE @string varchar(MAX)
    DECLARE @start INT
    DECLARE @end INT
    DECLARE @len INT

    SET @string=@pInputString
    --SET @string = 'the22478hollies12345TestAddressDr.789324-#345'
    SET @string = replace(@string, ' ' , '')

    WHILE PATINDEX('%[0-9]%',@string) <> 0
    BEGIN   
        SET @len = len(@string)
    --  PRINT @len

        set @start = PATINDEX('%[0-9]%',@string)
    --  PRINT @start

        SET @end= PATINDEX('%[^0-9]%',SUBSTRING(@string,@start,@len-@start))
    --  PRINT @end

        IF @end=0
            BEGIN
                SET @end=@len-@start
                SET @OutputString=SUBSTRING(@string,@start,@end+1)+'-'+@OutputString
                BREAK
            END
        ELSE 
            BEGIN 
                SET @OutputString=SUBSTRING(@string,@start,@end-1)+'-'+@OutputString
                SET @string=SUBSTRING(@string,@end+@start-1,@len-@end)
            END 

        --PRINT @string

        --PRINT @Output
        --PRINT '---------------------'
    END

    IF LEN(@OutputString)>0
        SET @OutputString=LEFT(@OutputString,LEN(@OutputString)-1) 
    --PRINT @OutputString

    RETURN @OutputString
END

How to find 5 digit number in a varchar?

3 Answers3

Linked

Related