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****)**

## 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

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

This is another method (needs little bit change)

Madhivanan

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

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

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

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

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

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

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 ?

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?

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.

Hi ,

How to sort the alphanumeric value in the table column.

Post some sample data with expected output

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.

Try this way

declare @s varchar(30)

set @s=’SN23024942;2009-01-01;DMM3403A’

select @s as string, parsename(REPLACE(@s,’;’,’.’),2) as date

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

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.

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

Very helpful function. Thanks for sharing

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?

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

Data Base :-Sql server 2000 and 2005 both