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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2005 – List all the database
Next Post
SQL SERVER – Query to Find First and Last Day of Current Month – Date Function

Related Posts

86 Comments. Leave new

  • 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

    Reply
  • As a side bar, I really hate what this forum does to nicely formatted code. ;-)

    Reply
  • This is another method (needs little bit change)

    Madhivanan

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • SRPBHUSHAN.K
    April 1, 2010 9:03 pm

    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…..

    Reply
  • SRPBHUSHAN.KAMBAMPATI
    April 2, 2010 9:38 am

    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…..

    Reply
  • 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 ?

    Reply
    • 1 Use computed column with your caluculation
      2 Use identity column that will take care of what you want to do
      3 Can you post some more informations?

      Reply
  • 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.

    Reply
  • Hi ,

    How to sort the alphanumeric value in the table column.

    Reply
  • 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.

    Reply
    • Try this way

      declare @s varchar(30)
      set @s=’SN23024942;2009-01-01;DMM3403A’
      select @s as string, parsename(REPLACE(@s,’;’,’.’),2) as date

      Reply
  • 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

    Reply
  • Blonde Stranger
    June 3, 2011 2:24 am

    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.

    Reply
  • Blonde Stranger
    June 3, 2011 7:13 pm

    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

    Reply
  • Very helpful function. Thanks for sharing

    Reply
  • 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?

    Reply
  • 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

    Reply
  • Data Base :-Sql server 2000 and 2005 both

    Reply

Leave a Reply

Menu