SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is very handy function when working with Alphanumeric String only. I have used this many times.

CREATE FUNCTION dbo.UDF_ParseAlphaChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET
@string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
END
SET
@string = @string
RETURN @string
END
GO

—-Test
SELECT dbo.UDF_ParseAlphaChars('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
GO

Result Set : ABCID4e5F6

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

85 thoughts on “SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

  1. Pingback: SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT Journey to SQL Authority with Pinal Dave

  2. Hi,
    I am Newbie to sql. I found this function interesting. I have something that needs similar functionality but I am unable to apply this function to my table.

    I have a table db2_coils where I have a column num_coil (this has both alphanumeric values Ex. B55436P) but what I need is just the numeric part of the num_coil(Ex. 55436) in a seperate column bse_num. Can I do that using above UDF, if yes How?

    • Yes, the only thing you should change in place of :
      SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @string) is
      SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string).

      By changing to SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string), you can get only numeric values.

  3. Hi,

    I am also newbie to sql. I would like to use this function to
    filter user data in some forms, so that I avoid SQL injections.

    However, I need to preserve the spaces in the data entry.
    Can someone help me how to preserve spaces in the output
    string???

    P.S. Pinal Dave Thanks for this is great function.

  4. Hi Guys and Girls,

    to get just the numeric part replace the WHILE BEGIN END with the following:

    WHILE @IncorrectCharLoc > 0
    BEGIN
    SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
    SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string)
    END

  5. Howdy Guys & Gals,

    The above function to return a number as a string works. Though, if you want a number, even if there are no actual numbers in the provided string, then include this before the final line “RETURN @string”

    IF(@string=”)
    SET @string = ’0′

    And, for those using SQL Server 2005, you might want to use RegEx instead; muhch faster.

  6. Hi

    can you help me with a function to get only the alphabets from a field.

    for eg: my field contains: 1001234567-Projectname-(1234567)

    i would need only the “Projectname” from this

    thanks

    • hi sheeba and all others who get this problem BY Using this Function .

      ALTER function dblmatrix(@Column varchar(30)) returns varchar(50)
      as
      Begin
      Declare @findex int
      Declare @lfindex int
      Declare @Beginse varchar(50)
      Declare @lastse varchar(50)
      Declare @lasts int
      Declare @BeginCount int
      declare @lngtrema int
      set @findex = patindex(‘%-%’,@Column)
      set @Beginse = substring(@Column,@findex+1,len(@Column))
      set @lfindex = patindex(‘%-%’,@Beginse)
      set @Beginse = substring(@Beginse,1,@lfindex-1)
      return @Beginse
      End

      select dbo.dblmatrix(alpha) from sheeba

      Data like this…
      1234-sheeba-2345
      1001234567-Projectname-(1234567)

      Out put data like this
      sheeba
      Projectname

      • hi sheeba and all others who get this problem BY Using this Function We get solve this .

        ALTER function dblmatrix(@Column varchar(30)) returns varchar(50)
        as
        Begin
        Declare @findex int
        Declare @lfindex int
        Declare @Beginse varchar(50)
        Declare @lastse varchar(50)
        Declare @lasts int
        Declare @BeginCount int
        declare @lngtrema int
        set @findex = patindex(‘%-%’,@Column)
        set @Beginse = substring(@Column,@findex+1,len(@Column))
        set @lfindex = patindex(‘%-%’,@Beginse)
        set @Beginse = substring(@Beginse,1,@lfindex-1)
        return @Beginse
        End

        select dbo.dblmatrix(alpha) from sheeba

        Data like this…
        1234-sheeba-2345
        1001234567-Projectname-(1234567)

        Out put data like this
        sheeba
        Projectname

  7. DECLARE @intFlag INT
    declare @string varchar(100)
    set @string=’1001234567-Projectname-(1234567)’

    declare @lsString varchar(100)
    set @lsString=”
    SET @intFlag = 1
    WHILE (@intFlag <=len(@string))
    BEGIN
    if PATINDEX(‘%[^0-9]%’, substring(@string,@intFlag,1))=1
    and substring(@string,@intFlag,1)!=’-’
    and substring(@string,@intFlag,1)!=’(‘
    and substring(@string,@intFlag,1)!=’)’
    begin
    set @lsString=@lsString+convert(varchar(100),substring(@string,@intFlag,1),105)
    end

    SET @intFlag = @intFlag + 1
    END
    PRINT @lsString
    GO

    Using this SQL u can get ONLY “Projectname”

    with Regards
    Ganesan.M

  8. @Ganesan

    declare @cmd varchar(100)
    set @cmd = ’1001234567-Projectname-(1234567)’

    select substring ( @cmd , charindex( ‘-’, @cmd)+1, (charindex( ‘(‘, @cmd) – charindex( ‘-’, @cmd)+1)-3)

    Result:
    Projectname

    This will work fine untill you have – before alphabets and -( at the end of the alphabets

    Thanks,
    Imran.

  9. Hi I hav a doubt …Hope you will help me.

    I hav a column property with fld P_Id (varchar) , P_Name .

    I want to select the Maximum Value of P_Id.if last

    P_Id is ‘P41′ then next should be ‘P42′.How can i get the maximun of 41.
    I tried
    SELECT max(SUBSTRING(P_Id, 2, LEN(P_Id)))AS P_Id
    FROM tblUser.
    but retrns 9 because of varchar…
    wht shld i doooooo.please help me

  10. Hi Dave,
    When I tried to convert varchar column value to int from a table, one of value length more than 12 chars, then i got error message:
    The conversion of the varchar value ’133261280299′ overflowed an int column. Maximum integer value exceeded.

    Can you please help?

  11. I would like to change my phone number into a numeric one. How can I do that?

    I have gone through the reading, but do not understand what it is all about. would you please advise me how to do it?

  12. Hi, thanks for the code. I’ve been looking for something like this for awhile.

    Could you tell me one thing, though?

    Why is it that if I leave the line:

    SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)

    The query runs and cleans the non-alphanumeric characters out quickly.

    But, if I want to add a space instead with:

    SET @string = STUFF(@string, @IncorrectCharLoc, 1, ‘ ‘)

    The query takes a long time to run. It seems that adding the space really negatively affects performance.

  13. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  14. To aLL,

    I have one table TABLE! , which contains one column named “EMPLOYEE NAME”.
    There are various values inside it.
    i want to check every field of EMPLOYEE NAME and find out if there is any Numeric Value inside.

    EG.
    EMPLOYEE NAME
    Billgates
    George
    A1dam –Here 1 is present in between
    Gem9ini –Here 9 is present in between

    i want to identify this types of name which contains the numeric value in the field.

  15. @Gates

    WITH
    EG([Employee Name])
    AS
    (
    SELECT ‘Billgates’ UNION ALL
    SELECT ‘George’ UNION ALL
    SELECT ‘A1dam’ UNION ALL
    SELECT ‘Gem9ini’
    )
    SELECT
    [Employee Name]
    FROM
    EG
    WHERE
    [Employee Name] LIKE ‘%[0-9]%’

  16. Thanx Pinal.

    And now that i changed my DNS records, i am receiving email from your website. Silly bug in Exchange with CNAMEs.

  17. Hey,

    I have a column called runtime where it has the runtime of movies, its a TEXT field, an most of the values inside are in this format:
    124m or 1546mins or 92 mins

    so how can i strip only the numbers from there??

    i really need ur help and thx alot in advance

  18. WHILE @IncorrectCharLoc > 0
    BEGIN
    SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
    SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string)
    END

  19. how can i get all numeric values as numeric value in a table ?

    i mean, the result set should be in a table format not scalar varchar value.

    your sample statement:
    SELECT dbo.UDF_ParseAlphaChars(‘ABC”_I+{D[]}4|:e;””5,/?6′)

    result should be:

    Number
    4
    5
    6

    Thanks

  20. I need to parse out the first set up numbers in a column before the first character in a string:
    example
    287PHY 10102 PHY 10106 BIO 32001 BIO 33501
    4568PHY 10102 PHY 10106 BIO 32001 BIO 33501
    32891PHY 10102 PHY 10106 BIO 32001 BIO 33501

    I need to parse out the
    287
    4568
    32891

    in a separate column.
    Can someone help me out?
    Thanks in advance.

    • Actually lori ur Problem can be solved in Number of ways and one of the Solution is this ………

      alter function splitdata(@Column varchar(30)) returns varchar(30)
      as
      Begin
      Declare @Sfindex int
      Declare @Beginse varchar(35)
      Declare @lastse varchar(35)
      Declare @lasts int
      Declare @BeginCount int
      declare @finalse varchar
      set @Sfindex = patindex(‘% %’,@Column)
      if @Sfindex !=0
      Begin
      set @Beginse = substring(@Column,1,@Sfindex-1)
      End
      else
      set @Sfindex = patindex(‘%P%’,@Column)
      if @Sfindex !=0
      Begin
      set @Beginse = substring(@Column,1,@Sfindex-1)
      End
      ELSE
      set @Sfindex = patindex(‘%B%’,@Column)
      if @Sfindex !=0
      Begin
      set @Beginse = substring(@Column,1,@Sfindex-1)
      End
      return @Beginse
      End

      alter function splitdataremain(@Column varchar(30)) returns varchar(30)
      as
      Begin
      Declare @Sfindex int
      Declare @Beginse varchar(35)
      Declare @lastse varchar(35)
      Declare @lasts int
      Declare @BeginCount int
      declare @finalse varchar
      set @Sfindex = patindex(‘% %’,@Column)
      if @Sfindex !=0
      Begin
      set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
      End
      else
      set @Sfindex = patindex(‘%P%’,@Column)
      if @Sfindex !=0
      Begin
      set @Beginse =replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
      End
      ELSE
      set @Sfindex = patindex(‘%B%’,@Column)
      if @Sfindex !=0
      Begin
      set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
      End
      return @Beginse
      End

      Data like this

      287PHY
      10102 PHY
      10106 PHY
      10106 BIO
      32001 BIO
      33501 BIO
      4568PHY
      10102 PHY

      select dbo.splitdata(Subject)as credits from lori
      select dbo.splitdataremain(Subject)as subject from Lori

      Points subject
      287 PHY
      10102 PHY
      10106 PHY
      10106 BIO
      32001 BIO
      33501 BIO
      4568 PHY
      10102 PHY

      • Hi Lori and who gets this problem

        alter function splitdata(@Column varchar(30)) returns varchar(30)
        as
        Begin
        Declare @Sfindex int
        Declare @Beginse varchar(35)
        Declare @lastse varchar(35)
        Declare @lasts int
        Declare @BeginCount int
        declare @finalse varchar
        set @Sfindex = patindex(‘% %’,@Column)
        if @Sfindex !=0
        Begin
        set @Beginse = substring(@Column,1,@Sfindex-1)
        End
        else
        set @Sfindex = patindex(‘%P%’,@Column)
        if @Sfindex !=0
        Begin
        set @Beginse = substring(@Column,1,@Sfindex-1)
        End
        ELSE
        set @Sfindex = patindex(‘%B%’,@Column)
        if @Sfindex !=0
        Begin
        set @Beginse = substring(@Column,1,@Sfindex-1)
        End
        return @Beginse
        End

        alter function splitdataremain(@Column varchar(30)) returns varchar(30)
        as
        Begin
        Declare @Sfindex int
        Declare @Beginse varchar(35)
        Declare @lastse varchar(35)
        Declare @lasts int
        Declare @BeginCount int
        declare @finalse varchar
        set @Sfindex = patindex(‘% %’,@Column)
        if @Sfindex !=0
        Begin
        set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
        End
        else
        set @Sfindex = patindex(‘%P%’,@Column)
        if @Sfindex !=0
        Begin
        set @Beginse =replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
        End
        ELSE
        set @Sfindex = patindex(‘%B%’,@Column)
        if @Sfindex !=0
        Begin
        set @Beginse = replace(substring(@Column,@Sfindex,len(@Column)),’ ‘,”)
        End
        return @Beginse
        End

        Data like this

        287PHY
        10102 PHY
        10106 PHY
        10106 BIO
        32001 BIO
        33501 BIO
        4568PHY
        10102 PHY

        select dbo.splitdata(Subject)as credits from lori
        select dbo.splitdataremain(Subject)as subject from Lori

        Points subject
        287 PHY
        10102 PHY
        10106 PHY
        10106 BIO
        32001 BIO
        33501 BIO
        4568 PHY
        10102 PHY

  21. @Adam

    Here is a quick CTE approach to putting each as a separate record.

    WITH
    Data(String) AS (SELECT ‘ABC”_I+{D[]}4|:e;””5,/?6′),
    CTE
    AS
    (
    SELECT
    0 X_Current,
    LEN(String) X_Max,
    ” Digit
    FROM
    Data
    UNION ALL
    SELECT
    CTE.X_Current + 1,
    CTE.X_Max,
    SUBSTRING(Data.String, CTE.X_Current, 1)
    FROM
    CTE,
    Data
    WHERE
    CTE.X_Current <= CTE.X_Max
    )
    SELECT
    Digit
    FROM
    CTE
    WHERE
    Digit LIKE '[0-9]'
    OPTION
    (MAXRECURSION 0);

  22. @Lori

    WITH
    Data(String)
    AS
    (
    SELECT ’287PHY 10102 PHY 10106 BIO 32001 BIO 33501′ UNION ALL
    SELECT ’4568PHY 10102 PHY 10106 BIO 32001 BIO 33501′ UNION ALL
    SELECT ’32891PHY 10102 PHY 10106 BIO 32001 BIO 33501′
    )
    SELECT
    SUBSTRING
    (
    String,
    1,
    PATINDEX(‘%[^0-9]%’, String) – 1
    )
    FROM
    Data;

  23. Hi Pinal/Folks,

    Can anyone please help me? I am stunned at this one.

    Let’s say I have the following records:

    INV0096
    INV0097
    INV0099
    INV0100
    INV0102
    INV0103

    How can I generate a SQL Script that will show me the gaps in the sequence?

    In such a way that the results will give me INV0098 and INV0101.
    Or even just the number 98, and 101.

    Thank you so much for your time.

    Best regards,

    Enrico

    • hi enrico And who get this problem. I think we can more easily get solve this problem…….. by using this Function….

      ALTER function misInvdata(@Column varchar(30)) returns varchar(30)
      as
      Begin
      Declare @Count int
      declare @afterCount int
      Declare @Sfindex int
      Declare @Beginse varchar(35)
      Declare @lastse varchar(35)
      Declare @lasts int
      declare @resulindex int
      Declare @BeginCount int
      declare @finalse varchar
      declare @storecolumn varchar(100)
      if @Column !=’Inv005′
      Begin
      set @Sfindex = patindex(‘%v%’,@column)
      set @Beginse = substring(@Column,1,@Sfindex)
      set @lastse = substring(@Column,@Sfindex+1,len(@Column))
      set @lasts = cast(@lastse as int)
      set @lasts =@lasts + 1
      set @lastse =cast(@lasts as varchar(25))
      while(len(@lastse)<3)
      Begin
      set @lastse ='0'+ @lastse
      End
      set @Beginse =@Beginse + @lastse
      End
      return @Beginse
      End
      and by this query…..

      select dbo.misInvdata(Invdata) from Invdata where dbo.misInvdata(Invdata) not In (Select Invdata from Invdata)

      DATA LIKE THIS …….

      Inv001
      Inv002
      Inv004
      Inv005

      OUTPUT:Inv003

      nOte:the Last row should be omitted in the Function thats hardcoded here……… or You can do this (omit last row) in query too in dbo.misInvdata(Invdata).

  24. @enrico

    WITH
    Data(Datum)
    AS
    (
    SELECT ‘INV0096′ UNION ALL
    SELECT ‘INV0097′ UNION ALL
    SELECT ‘INV0099′ UNION ALL
    SELECT ‘INV0100′ UNION ALL
    SELECT ‘INV0102′ UNION ALL
    SELECT ‘INV0103′
    ),
    CTE
    AS
    (
    SELECT
    CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start,
    CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish
    FROM
    Data
    UNION ALL
    SELECT
    Start + 1,
    Finish
    FROM
    CTE
    WHERE
    Start < Finish
    )
    SELECT
    Common.Formatted
    FROM
    CTE
    CROSS APPLY(SELECT 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)) Common(Formatted)
    WHERE
    NOT EXISTS
    (
    SELECT
    *
    FROM
    Data
    WHERE
    Data.Datum = Common.Formatted
    )
    OPTION
    (MAXRECURSION 0);

    The CROSS APPLY is just nice, but not required. Without it, the query is very similar:

    WITH
    Data(Datum)
    AS
    (
    SELECT 'INV0096' UNION ALL
    SELECT 'INV0097' UNION ALL
    SELECT 'INV0099' UNION ALL
    SELECT 'INV0100' UNION ALL
    SELECT 'INV0102' UNION ALL
    SELECT 'INV0103'
    ),
    CTE
    AS
    (
    SELECT
    CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start,
    CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish
    FROM
    Data
    UNION ALL
    SELECT
    Start + 1,
    Finish
    FROM
    CTE
    WHERE
    Start < Finish
    )
    SELECT
    'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)
    FROM
    CTE
    WHERE
    NOT EXISTS
    (
    SELECT
    *
    FROM
    Data
    WHERE
    Data.Datum = 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)
    )
    OPTION
    (MAXRECURSION 0);

  25. @enrico,

    You can find Missing values with Recursive CTE as follwing, It will work only with SQL 2005 and above.

    DECLARE @Test TABLE (Data VARCHAR(10))
    INSERT INTO @test
    SELECT ‘INV0096′
    UNION ALL
    SELECT ‘INV0097′
    UNION ALL
    SELECT ‘INV0099′
    UNION ALL
    SELECT ‘INV0100′
    UNION ALL
    SELECT ‘INV0102′
    UNION ALL
    SELECT ‘INV0103′
    UNION ALL
    SELECT ‘INV0106′
    UNION ALL
    SELECT ‘INV0110′

    ;with cte1 as(
    SELECT CAST(RIGHT(Data,4) AS INT) As RowID
    FROM @Test
    ), Missing as(
    SELECT MIN(RowID) AS MissNum,
    MAX(RowID) AS MaxID
    FROM Cte1
    UNION ALL
    SELECT MissNum + 1,
    MaxID
    FROM Missing
    WHERE MissNum < MaxID
    )
    SELECT missnum
    FROM Missing
    LEFT JOIN cte1 tt on tt.Rowid = Missing.MissNum
    WHERE tt.Rowid is NULL
    OPTION (MAXRECURSION 0);

    Let us know if it helps you.

    Thanks,

    Tejas
    (SQLYoga.com)

  26. Hi Brian/Tejas,

    Thanks for that!
    That worked beautiful on SQL2005! You guys are awesome.

    On the off chance, is there a way to get the same result on SQL2000?

    I definitely appreciate your time guys. I can’t even begin to express my thanks for helping me out on this.

    I’ll look forward to your thoughts on the same issue for SQL2000.

    Thanks again.

    Regards,

    enrico

    • I have seen the Posted things for this Problem No body has given a perfec’yet’ Out put than this.But please do not Copy it nor Mar it…This is Called Selective-Insertion algorithm for Missing Numbers By Bhushan works in all Versions………..tested…
      Declare @maxnum int
      Declare @sindex int
      declare @disprec varchar(500)
      Declare @Beginse varchar(500)
      Declare @originalstr varchar(8000)
      Declare @originaldisp varchar(8000)
      Declare @sdispindex int
      Declare @strlen int
      Declare @searchd int
      Declare @searchst varchar(30)
      Declare @origlen int
      Declare @concatinaldisp varchar(30)
      Declare @topminrumdum int
      select @maxnum = max(cast(Right(Mxvar,4)as int)) from Maxvar
      select Right(Mxvar,4)+ 1 as Mxvar into #temptested from Maxvar
      select @disprec = cast(Mxvar as varchar(45)) from #temptested
      select @Beginse = Mxvar from Maxvar
      select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
      select @Beginse =@Beginse+’-’ + Right(Mxvar,4) from Maxvar
      set @sdispindex = patindex(‘-’,@disprec)
      set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
      set @sindex =patindex(‘%-%’,@Beginse)
      if @sindex > 0
      Begin
      set @originalstr = substring(@Beginse,@sindex+1,len(@Beginse))
      End
      set @origlen=patindex(‘%-%’,@originaldisp)
      set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
      set @strlen =len(@originaldisp)

      while(@strlen >0)
      Begin
      set @searchd = patindex(‘%-%’,@originaldisp)
      if @searchd = 0
      Begin
      set @originaldisp=”
      update #temptested set Mxvar=Mxvar+1 where Mxvar=Mxvar
      select @topminrumdum = min(Mxvar) from #temptested
      select @maxnum =max(cast(Right(Mxvar,4)as int)) from Maxvar
      select @disprec = cast(Mxvar as varchar(45)) from #temptested
      select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
      set @sdispindex = patindex(‘-’,@disprec)
      set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
      set @origlen=patindex(‘%-%’,@originaldisp)
      set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
      set @searchd = patindex(‘%-%’,@originaldisp)

      if(@maxnum @topminrumdum or @maxnum != @topminrumdum)
      Begin
      set @concatinaldisp =substring(@originaldisp,1,@searchd)
      set @concatinaldisp = replace(@concatinaldisp,’-’,”)

      while(len(@concatinaldisp) @maxnum)
      Begin
      set @sindex = 1
      End
      Else
      set @sindex = charindex(@concatinaldisp,@originalstr)
      if @sindex =0
      Begin
      set @originalstr = @originalstr + ‘-’ +@concatinaldisp
      insert into #temper(mxvar)values(@concatinaldisp)
      –print @concatinaldisp
      –print @originalstr
      End
      End
      End
      End

  27. @Enrico,

    I developed this script for SQL 2000.

    In this script, there is limitation, that it will give you only one missing value.

    If we miss 104,105 (means more than one value we missed) then it will return only one.

    DECLARE @Test TABLE (Data VARCHAR(10))
    INSERT INTO @test
    SELECT ‘INV0096′
    UNION ALL
    SELECT ‘INV0097′
    UNION ALL
    SELECT ‘INV0099′
    UNION ALL
    SELECT ‘INV0100′
    UNION ALL
    SELECT ‘INV0102′
    UNION ALL
    SELECT ‘INV0103′
    UNION ALL
    SELECT ‘INV0106′
    UNION ALL
    SELECT ‘INV0110′

    CREATE TABLE #test (ID INT IDENTITY, RowID INT)
    INSERT INTO #test(RowID)
    SELECT CAST(RIGHT(Data,4) AS INT) As RowID
    FROM @Test
    ORDER BY CAST(RIGHT(Data,4) AS INT)

    SELECT t1.RowID + 1
    FROM #test t1
    INNER JOIN #test t2 ON t1.ID + 1 = t2.ID
    AND t1.RowID + 1 t2.RowID

    DROP TABLE #test

    I will try to make it as I made for SQL 2005, but I think it will take time.

    Let me know if it helps you.

    Thanks,

    Tejas
    (SQLYOGA.com)

  28. Hi Tejas,

    Thanks for that mate!

    I really appreciate the assistance!

    Thanks Pinal for putting up such an awesome site!!

    Regards,

    enrico

  29. @enrico
    i just write this and good to see that it also working in SQL 2000 hope will be helpful to you,,,

    DECLARE @Test TABLE (Data VARCHAR(10))
    INSERT INTO @test
    SELECT ‘INV0096′
    UNION ALL
    SELECT ‘INV0097′
    UNION ALL
    SELECT ‘INV0099′
    UNION ALL
    SELECT ‘INV0100′
    UNION ALL
    SELECT ‘INV0102′
    UNION ALL
    SELECT ‘INV0103′
    UNION ALL
    SELECT ‘INV0106′
    UNION ALL
    SELECT ‘INV0110′

    select number from master..spt_values where
    number not in (select right(data,4) from @test) and
    number between (select min(right(data,4)) from @test)
    and (select max(right(data,4)) from @test) and TYPE=’P’

  30. Hi
    I am facing a problem while using the ISNUMERIC fucntion.
    I have a huge table. One of the columns is Global_Product_id. now this is in varchar. I have created a backup of this same table with similar structure except that the column Global_ProductId is now Bigint

    Now using the query below to insert value in that column
    SELECT CASE WHEN ISNUMERIC(LTRIM(RTRIM(GLOBAL_PRODUCT_ID)))=1 THEN CAST(LTRIM(RTRIM(GLOBAL_PRODUCT_ID)) AS BIGINT)
    ELSE NULL END GLOBAL_PRODUCT_ID
    FROM

    gives me the error – error converting varchar to numeric.

    It seems ISNUMERIC is unable to detect the field properly. There may be some ascii chachetres in the columns. iT was loaded form flat file.

    Can you help me in resolving this issue?

  31. Hello ,
    I have a column having nvarchar datatype which contain values like = 1,2,3,4..,100,101..,1010…, MCA1, MCA2,…,MBA1, MBA2…. etc like this. I want to retrieve only all integer data just like (1,2,3,4,..,100,101..,1010..) Not all data.. how to retrieve it.. I am using Substring(MCA10 , patindex(‘%[0-9]%’,MCA10),len(MCA10)) to retrieve Numeric part of alphanumeric number. But i want only integer value (1,2,3,4,….) not alphanumeric value.

    • Quite a few posters have asked for a function that just returns the numbers from a string.

      This is an adaptation of Pinal’s function that will do return a string containing only the characters 0-9 from the input string.

      CREATE FUNCTION dbo.fnctParseNumericChars
      (
      @string VARCHAR(8000)
      )
      RETURNS VARCHAR(8000)
      AS
      BEGIN
      DECLARE @IncorrectCharLoc SMALLINT
      SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string)
      WHILE @IncorrectCharLoc > 0
      BEGIN
      SET @string = STUFF(@string, @IncorrectCharLoc, 1, ”)
      SET @IncorrectCharLoc = PATINDEX(‘%[^0-9]%’, @string)
      END
      SET @string = @string
      RETURN @string
      END
      GO

  32. @Brian Tkatch,

    You might want to reconsider… ISNUMERIC should never be used as ISALLDIGITS… it wasn’t designed for that.

    SELECT ISNUMERIC(’2e3′),
    ISNUMERIC(’2d3′),
    ISNUMERIC(’1,000′),
    ISNUMERIC(CHAR(9)),
    ISNUMERIC(‘$’),
    ISNUMERIC(‘-’)

  33. Mattering only where millions of rows are present, the following modification of Pinal’s good code is about 11 seconds faster on a million rows on a 7 year old single 1.8Ghz CPU…

    CREATE FUNCTION dbo.AlphaNumeric
    — Modified by Jeff Moden
    (@String VARCHAR(8000))
    RETURNS VARCHAR(8000) AS
    BEGIN
    DECLARE @IncorrectCharLoc SMALLINT
    SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @String)
    WHILE @IncorrectCharLoc > 0
    SELECT @string = STUFF(@String, @IncorrectCharLoc, 1, ”),
    @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @String)
    RETURN @string
    END

  34. hi ernico and all others who get this problem……

    Use this

    ALTER function misInvdata(@Column varchar(30)) returns varchar(30)
    as
    Begin
    Use this function

    Declare @Sfindex int
    Declare @Beginse varchar(35)
    Declare @lastse varchar(35)
    Declare @lasts int
    Declare @BeginCount int
    declare @finalse varchar
    set @Sfindex = patindex(‘%v%’,@Column)
    set @Beginse = substring(@Column,1,@Sfindex)
    set @lastse = substring(@Column,@Sfindex+1,len(@Column))
    set @lasts = cast(@lastse as int)
    set @lasts =@lasts + 1
    set @lastse =cast(@lasts as varchar(25))
    while(len(@lastse)<3)
    Begin
    set @lastse ='0'+ @lastse
    End
    set @Beginse =@Beginse + @lastse
    return @Beginse
    End

    and Use this query

    select dbo.misInvdata(Invdata) from Invdata where dbo.misInvdata(Invdata) not in( select invdata from invdata union select max(dbo.misInvdata(invdata)) from invdata)

    DAta like this

    Inv001
    Inv002
    Inv004
    Inv005
    Inv007

    Output data like this

    Inv003
    Inv006

  35. use this Function query No need to hard code any thing…

    ALTER function misInvdata(@Column varchar(30)) returns varchar(30)
    as
    Begin
    Declare @Sfindex int
    Declare @Beginse varchar(35)
    Declare @lastse varchar(35)
    Declare @lasts int
    declare @finalse varchar
    set @Sfindex = patindex(‘%v%’,@Column)
    set @Beginse = substring(@Column,1,@Sfindex)
    set @lastse = substring(@Column,@Sfindex+1,len(@Column))
    set @lasts = cast(@lastse as int)
    set @lasts =@lasts + 1
    set @lastse =cast(@lasts as varchar(25))
    while(len(@lastse)<3)
    Begin
    set @lastse ='0'+ @lastse
    End
    set @Beginse =@Beginse + @lastse
    return @Beginse
    End

    and Use this query

    select dbo.misInvdata(Invdata) from Invdata where dbo.misInvdata(Invdata) not in( select invdata from invdata union select max(dbo.misInvdata(invdata)) from invdata)

    Data like this
    Inv001
    Inv002
    Inv004
    Inv005
    Inv007

    Output data like this
    Inv003
    Inv006

  36. Hi Enrico and all who get this Problem I have seen several posts(solutions) for this problem.But No post have been provided a Pefec’yet’ solution Better than this.I request You do not copy this or Nor mar it.just for analysis i am giving this.This is Called Increament-Selection-Itterative ‘Batch’ for Missing Sequence Number by Bhushan.kambampati.

    Declare @minnum int
    Declare @maxnum int
    Declare @sindex int
    declare @disprec varchar(500)
    Declare @Beginse varchar(500)
    Declare @originalstr varchar(8000)
    Declare @originaldisp varchar(8000)
    Declare @sdispindex int
    Declare @strlen int
    Declare @searchd int
    Declare @searchst varchar(30)
    Declare @origlen int
    Declare @concatinaldisp varchar(30)
    Declare @topminrumdum int
    select @maxnum = max(cast(Right(Mxvar,4)as int)) from Maxvar
    select @minnum =min(Right(Mxvar,4)) from Maxvar
    select Right(Mxvar,4)+ 1 as Mxvar into #temptested from Maxvar
    select @disprec = cast(Mxvar as varchar(45)) from #temptested
    select @Beginse = Mxvar from Maxvar
    select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
    select @Beginse =@Beginse+’-’ + Right(Mxvar,4) from Maxvar
    set @sdispindex = patindex(‘-’,@disprec)
    set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
    set @sindex =patindex(‘%-%’,@Beginse)
    if @sindex > 0
    Begin
    set @originalstr = substring(@Beginse,@sindex+1,len(@Beginse))
    End
    set @origlen=patindex(‘%-%’,@originaldisp)
    set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
    set @strlen =len(@originaldisp)
    while(@strlen >0)
    Begin
    set @searchd = patindex(‘%-%’,@originaldisp)
    if @searchd = 0
    Begin
    set @originaldisp=”
    update #temptested set Mxvar=Mxvar+1 where Mxvar=Mxvar
    select @topminrumdum = min(Mxvar) from #temptested
    select @maxnum =max(cast(Right(Mxvar,4)as int)) from Maxvar
    select @disprec = cast(Mxvar as varchar(45)) from #temptested
    select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
    set @sdispindex = patindex(‘-’,@disprec)
    set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
    set @origlen=patindex(‘%-%’,@originaldisp)
    set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
    set @searchd = patindex(‘%-%’,@originaldisp)
    if(@maxnum @topminrumdum or @maxnum != @topminrumdum)
    Begin
    set @concatinaldisp =substring(@originaldisp,1,@searchd)
    set @concatinaldisp = replace(@concatinaldisp,’-’,”)
    while(len(@concatinaldisp) @maxnum)
    Begin
    set @sindex = 1
    End
    Else
    set @sindex = charindex(@concatinaldisp,@originalstr)
    if @sindex =0
    Begin
    set @originalstr = @originalstr + ‘-’ +@concatinaldisp
    insert into #temper(mxvar)values(@concatinaldisp)
    –print @concatinaldisp
    –print @originalstr
    End
    End
    End
    End

    –create Table #temper
    –(
    –mxvar varchar(35)
    –)
    –drop table #temptested
    –Delete from Maxvar where Mxvar=’Inv9999′

    – insert into Maxvar(Mxvar)values(‘Inv1599′)

    select distinct(Mxvar) from #temper order by Mxvar asc

  37. Hi Enrico and all who get this Problem I have seen several posts(solutions) for this problem.But No post have been provided a Pefec’yet’ solution Better than this.I request You do not copy this or Nor mar it.just for analysis i am giving this.This is Called Increament-Selection-Itterative ‘Batch’ for Missing Sequence Number by Bhushan.kambampati.

    Declare @minnum int
    Declare @maxnum int
    Declare @sindex int
    declare @disprec varchar(500)
    Declare @Beginse varchar(500)
    Declare @originalstr varchar(8000)
    Declare @originaldisp varchar(8000)
    Declare @sdispindex int
    Declare @strlen int
    Declare @searchd int
    Declare @searchst varchar(30)
    Declare @origlen int
    Declare @concatinaldisp varchar(30)
    Declare @topminrumdum int
    select @maxnum = max(cast(Right(Mxvar,4)as int)) from Maxvar
    select @minnum =min(Right(Mxvar,4)) from Maxvar
    select Right(Mxvar,4)+ 1 as Mxvar into #temptested from Maxvar
    select @disprec = cast(Mxvar as varchar(45)) from #temptested
    select @Beginse = Mxvar from Maxvar
    select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
    select @Beginse =@Beginse+’-’ + Right(Mxvar,4) from Maxvar
    set @sdispindex = patindex(‘-’,@disprec)
    set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
    set @sindex =patindex(‘%-%’,@Beginse)
    if @sindex > 0
    Begin
    set @originalstr = substring(@Beginse,@sindex+1,len(@Beginse))
    End
    set @origlen=patindex(‘%-%’,@originaldisp)
    set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
    set @strlen =len(@originaldisp)
    while(@strlen >0)
    Begin
    set @searchd = patindex(‘%-%’,@originaldisp)

    if @searchd = 0
    Begin
    set @originaldisp=”
    update #temptested set Mxvar=Mxvar+1 where Mxvar=Mxvar
    select @topminrumdum = min(Mxvar) from #temptested
    select @maxnum =max(cast(Right(Mxvar,4)as int)) from Maxvar
    select @disprec = cast(Mxvar as varchar(45)) from #temptested
    select @disprec = @disprec + ‘-’ + cast(Mxvar as varchar(45)) from #temptested
    set @sdispindex = patindex(‘-’,@disprec)
    set @originaldisp = substring(@disprec,@sdispindex+1,len(@disprec))
    set @origlen=patindex(‘%-%’,@originaldisp)
    set @originaldisp = substring(@originaldisp,@origlen+1,len(@originaldisp))
    set @searchd = patindex(‘%-%’,@originaldisp)

    if(@maxnum @topminrumdum or @maxnum != @topminrumdum)
    Begin
    set @concatinaldisp =substring(@originaldisp,1,@searchd)
    set @concatinaldisp = replace(@concatinaldisp,’-’,”)

    while(len(@concatinaldisp) @maxnum)
    Begin
    set @sindex = 1
    End
    Else
    set @sindex = charindex(@concatinaldisp,@originalstr)
    if @sindex =0
    Begin
    set @originalstr = @originalstr + ‘-’ +@concatinaldisp
    insert into #temper(mxvar)values(@concatinaldisp)
    –print @concatinaldisp
    –print @originalstr
    End
    End
    End
    End

    –create Table #temper
    –(
    –mxvar varchar(35)
    –)
    –drop table #temptested
    –Delete from Maxvar where Mxvar=’Inv9999′

    – insert into Maxvar(Mxvar)values(‘Inv1599′)

    select distinct(Mxvar) from #temper order by Mxvar asc

  38. The Algorithm is More very Much parllel to the Selection and insertion Sort and Merge sorts,Heap sorts of some other sorting Techniques.

    Algorithm for Increament-selection and Itteration to Find the Missing Sequence Number.

    Step:1 Let N-be the values or terms of the Tabular data.Make Each and Every value to N+1 values of the tabular data

    Step:2 Check Each and Every value which is N+1 but Not in N-values.if Such values are found.Merge into N-values string Go to step4

    step3:Update the Each values of N+1 terms increament by ’1′ go to step2

    step4: check the Max value of the N-tabular data. And Updated value of the Minimum or first row of N+1 tabular data is Equal or Greater than Max (of n- term tabular data). if so Exit the Loop else go to step 3

    Note: Omit the Last value or Last row in Each N+1 increament values when Your comparing the N-term strings And this algorithm only works if you know the Max value of the N-term.and Minimum row of the each updated by N+1 terms( increamented by 1).(In computation it is possible).

    And there should be No restrictions for the Missing sequence(It may differ by 1 ,1000,1,00,000 e.t.c).

    Advantages: You can work it what ever be the order of the sequence.They may be Increasing or Decreasing Order.still You find the Sequence Numbers….

    Thanks ……..
    Bhushan.kambampati…..

  39. The Algorithm is More very Much parllel to the Bubble, Selection and insertion Sort and Merge sorts,Heap sorts or some other sorting Techniques.

    Algorithm for Increament-selection – Itteration to Find the Missing Sequence Number.

    Step:1 Let N-be the values or terms of the Tabular data.Make Each and Every value to N+1 values of the tabular data

    Step:2 Check Each and Every value which is N+1 but Not in N-values.if Such values are found.Merge into N-values Go to step4

    step3:Update the Each values of N+1 terms increament by ‘1′ go to step2

    step4: check the Max value of the N-tabular data. And Updated value of the Minimum or first row of N+1 tabular data is Equal or Greater than Max (of n- term tabular data). if so Exit the Loop else go to step 3

    Note: Omit the Last value or Last row in Each N+1 increament values when Your comparing the N-term tabular data.And this algorithm only works if you know the Max value of the N-term.and Minimum row value (or) first value of the each updated N+1 terms( increamented by 1).(In computation it is possible).

    And there should be No restrictions for the Missing sequence(It may differ by 1 ,1000,1,00,000 e.t.c in each N-values of Tabular data).

    Advantages: You can work it whatever be the order of the sequence Numbers.They may be Increasing or Decreasing Order.still You find the Missing Sequence Numbers….

    Thanks….
    Bhushan.kambampati…..

  40. hi friends,
    can any one help me pl!!
    in sql 2005.i have created a table with a column name as rid,total,cur_value and balance.
    In balance column, this total value minus(-) cur_value is stored.
    1)now,after each transaction i wanna store this balance value into the total column again automatically…how to do it?
    2)how to increment the rid value lik (1,2,3,4,5…)after each transaction is executed?
    3) how to get a particular value in a table using aggregate as condition ?

  41. Great Post and Comments !!!

    I just thought, how will I extract only numeric. And wooo…. I got it from one of the comment (from George Tzirtzi).

    And one of the commentor told that, “We can also use it to avaoid SQL injection”. I will try this too.

    I read all your Top Post today. You have large amout of comments in it. I learned a lot from articles as well as comments by other SQL experts.

  42. Hi, From seeing the above post I got to know that from SQL u can parse the alpha numeric value. In my case, I have a field which has charecters like “SN23024942;2009-01-01;DMM3403A”. This field is a combination of a serial number, date and the model number of the parts of an inventory. I actually want to just remove the date part 2009-01-01 and store it in a field which is like a timestamp “2009-01-01 00:00″ so that I could track the parts by using date. Is this possible from any of the above functions?

    Thanks in advance.

  43. Hi there,

    I hope you guys can help me! I am so stuck!

    I have a alphanumeric column call “DWG Rev”. Revisions start at A, B C etc. Once the drawings is accepted the revisions become numbers i.e 1, 2, 3.

    I need to get the Max of revision where numbers are latest.

    SO

    DWG Number | Drawing Date | DWG Rev
    Group by Last Max?

    This is where I am having the problem, when using aggregate function I would like to get the latest drawing by selecting the last date however, I would like to show the revision but not sure how. Please help

  44. I’m trying to sort on an odd alpha numeric value. I’ve tried LEN and SUBSTRING but I just can’t seem to find the correct combination. Here is the list of values that I get, followed by what I want to get.

    DESC sort
    170-151-180(1)(d)
    170-151-180(10)
    170-151-180(10)(a)
    170-151-180(10)(b)
    170-151-180(11)
    170-151-180(2)

    THIS IS WHAT I WOULD LIKE IT TO SORT ON.

    170-151-180(1)(d)
    170-151-180(2)
    170-151-180(10)
    170-151-180(10)(a)
    170-151-180(10)(b)
    170-151-180(11)

    Any help please.

  45. I was able to get the sort worked out. Here is my solution, but I’d be interested to see other solutions as well.

    declare @r1 table (ANUM varchar(500),ORD varchar(500))

    insert into @r1(ANUM ,ORD)
    select ANUM ,replace(ANUM ,’ ‘,”) from refTBL where ((PATINDEX(‘%)%’, ANUM ))-(PATINDEX(‘%(%’, ANUM ))) != 2
    union
    SELECT ANUM ,replace(REPLACE(ANUM ,’(‘,’(0′),’ ‘,”) from refTBL where ((PATINDEX(‘%)%’, ANUM ))-(PATINDEX(‘%(%’, ANUM ))) = 2

    SELECT ANUM from @r1
    order by ORD

  46. Suppose I Want To Add ‘i’ Word At The End Of ‘DELH’ How Can I Do IT?
    I Am Using Following Function But Results Comes Null

    Select Stuff(‘DELH’,5,0,’i’)

    Any help please?

  47. Hi,
    how to order on this condition :-
    Name(varchar)(coloum name)
    Ram
    Mohan
    1234 8876
    Abhi
    1234 8877

    and i want to order like this :-
    Name
    Abhi
    Mohan
    Ram
    1234 8876
    1234 8877

    Thanks

  48. Can you explain why you have these two lines?

    SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @string) — 2nd one
    SET @string = @string

    I created a version without them and it works fine. I don’t see the point of these lines, but if I am missing something I would like to know. Either way, it is a great solution. Thank you.

  49. Hi ,

    I am new to sql

    Could some one help me with parsing function in SQL server in splitting the value form comma separated string.

    The output of the column looks like…

    Patient Othernumber
    —————————–
    123
    567,56e3,785
    746
    66y,8968g,5656,4435

    i wan to split this output.

    Thank you.

  50. Hi,

    Thank you very much for your quick response. I have written a function to split these output and it works fine for me.

  51. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

  52. Pingback: SQL SERVER – Find Gaps in The Sequence | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s