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