Article: String Functions: Incrementing a Number in a Char

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: String Functions: Incrementing a Number in a Char

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-20 : 03:10:25

Derrick writes "I have a value : 'PIT000' I need to increment this value i.e. PIT001, PIT002, etc until PIT999. How do I do this using a SQL query. The next step is: once you reach PIT999, the value needs to change to PIU000!!" We certainly can do this in SQL Server and it gives us a chance to talk about some neat string functions too.

Article Link.

tribune
Posting Yak Master

105 Posts

Posted - 2001-12-21 : 00:15:55

I thought this was a fun topic, here is something I wrote you might find useful. It is very basic and can be customized to have constants for product code (string) length for added functionaly. Its in vbscript but can easily be converted to sql.

<%
Response.Write "Incrementing PIT001 = " & IncProductString("PIT001")
Response.Write "Incrementing PIT002 = " & IncProductString("PIT002")
Response.Write "Incrementing PIT999 = " & IncProductString("PIT999")
Response.Write "Incrementing AAZ999 = " & IncProductString("AAZ999")
Response.Write "Incrementing AZZ999 = " & IncProductString("AZZ999")
Response.Write "Incrementing ZZZ999 = " & IncProductString("ZZZ999")

Function IncProductString(str)
Dim intOldNumberHalf
Dim intNewNumberHalf
Dim strStringHalf

If Len(str) <> 6 Then
IncProductString = -1
Exit Function
End If

strOldNumberHalf = Right(str,3)
strNewNumberHalf = IncNumberString(Right(str,3))
strStringHalf = Left(str,3)

If CInt(strOldNumberHalf) > CInt(strNewNumberHalf) Then
IncProductString = UCase(IncString(LCase(strStringHalf))) & strNewNumberHalf
Else
IncProductString = strStringHalf & strNewNumberHalf
End If
End Function

' Recursively increments an alphabetic value of a lower case string, and rolls
' over at the string's length.
' ie. "hello" to "hellp", and "azz" to "baa", "zzz" to "aaa"
Function IncString(str)
If Len(str) = 0 Then Exit Function End If
If Right(str,1) = "z" Then
IncString = IncString(Left(str,Len(str)-1)) & "a"
Else
'"zza" to "zz" & "b"
IncString = Left(str,Len(str)-1) & Chr(Asc(Right(str,1))+1)
End If
End Function

' Increments a numeric string and returns a 3 digit string
' prefixed with 0s if necessary
Function IncNumberString(str)
IncNumberString = Right("000" & CStr(CInt(str) + 1),3)
End Function
%>

avan
Starting Member

1 Post

Posted - 2003-01-14 : 00:49:11

Hi Derrick
Here is my solution for your problem.Hope so it's help u.

if object_id('st_display_incr_char') is Not null
drop proc st_display_incr_char
go
create procedure st_display_incr_char
as
begin
declare @intialstr varchar(1000)
declare @curstr varchar(1000)
declare @tmp varchar(10)
declare @i int
set @intialstr = 'PIA000'
set @tmp = @intialstr
set @i = 0
print 'The series begins '+ char(13)
while (ascii(substring(@intialstr,3,1)) <= ascii('Z'))
begin
set @tmp = substring(@tmp,4,len(@tmp))
if (@tmp = '999')--check if loop has reached 999
begin
if (substring(@intialstr,3,1)= 'Z')
return
set @intialstr = rtrim(substring(@intialstr,0,3)) + char(convert(smallint,ascii(substring(@intialstr,3,1)))+ 1) + '000'
set @tmp = @intialstr
end
else
begin
set @curstr = rtrim(substring(@intialstr,0,4))
if len(ltrim(str(convert(smallint ,@tmp) + 1 ))) = 1
set @tmp = '00'+ ltrim(str(convert(smallint ,@tmp) + 1 ))
if len(ltrim(str(convert(smallint ,@tmp) + 1 ))) = 2
set @tmp = '0'+ ltrim(str(convert(smallint ,@tmp) + 1 ))
if len(ltrim(str(cast(@tmp + 1 as smallint)))) = 3
set @tmp = ltrim(str(cast(@tmp + 1 as smallint)))
set @curstr = @curstr + @tmp
set @tmp = @curstr
set @i = @i + 1
end
print '****** ' + @tmp + ' ******'
end
end

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2003-01-14 : 02:10:39

We had a similar one before it was solved with approx. 7 lines of code.

Search the forum for topics about 14 days old.

gates_micro
Starting Member

29 Posts

Posted - 2004-05-21 : 07:43:46

Here is the way. Its very simple.

create procedure display_char as
BEGIN

declare @initchr varchar(1000)
declare @curchr varchar(1000)
declare @tmpchr varchar(1000)
declare @i int
declare @ascval int
declare @sngchr char(5)

set @initchr = 'PIA000'
set @tmpchr= @initchr
set @i=0
set @ascval=65
set @sngchr='false'

while (@ascval<91)
begin
if(@i<=9)
set @curchr = substring(@tmpchr,0,len(@tmpchr))
else
if (@i>9 and @i<100)
set @curchr = substring(@tmpchr,0,len(@tmpchr)-1)
else
if (@i>99 and @i<=1000)
begin
set @curchr = substring(@tmpchr,0,len(@tmpchr)-2)
if(@i=1000)
begin
set @i=0
set @ascval = @ascval + 1
set @curchr = substring(@tmpchr,0,len(@tmpchr)-3)
set @curchr = @curchr + char(@ascval) + '00'
if(@ascval=91)
set @sngchr='true'
end
end

set @tmpchr = @curchr + convert(char(3),@i)
if(@sngchr = 'false')
print @tmpchr
set @i = @i + 1
end

END

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-21 : 09:54:08

Tally table:

declare 
	@val char(2),
	@Letter char(1)

select 
	@val = 'PI', 
	@letter =  'A'

select d.alpha + right('000' + convert(varchar(3),n) ,3)
from
(
	select @val + char(ascii(@Letter) + n) alpha
	from numbers n
	where n.n <= 25
) d
cross join numbers n 
where n.n < 1000

gates_micro
Starting Member

29 Posts

Posted - 2004-05-22 : 13:36:58

Nice way to do it.

rohit.guru2004
Starting Member

2 Posts

Posted - 2010-07-30 : 04:00:09

if object_id('st_display_incr_char') is Not null
drop proc st_display_incr_char
go
create procedure st_display_incr_char
as
begin
declare @tname varchar(25)
declare @tname1 varchar(25)
declare @tname2 varchar(25)

declare @intialstr varchar(100)
declare @curstr varchar(100)
declare @tmp varchar(10)
declare @i int
set @intialstr = 'MP00'
set @tmp = @intialstr
set @i = 0
print 'The series begins '+ char(13)
while (ascii(substring(@intialstr,2,1)) <= ascii('Z'))
begin
set @tmp = substring(@tmp,3,len(@tmp))
if (@tmp = '230')--check if loop has reached 999
begin
-- if (substring(@intialstr,3,1)= 'Z')
return
set @intialstr = rtrim(substring(@intialstr,0,3)) + char(convert(smallint,ascii(substring(@intialstr,3,1)))+ 1) + '000'
set @tmp = @intialstr
end
else
begin
set @curstr = rtrim(substring(@intialstr,0,3))
if len(ltrim(str(convert(smallint ,@tmp) + 1 ))) = 1
set @tmp = '00'+ ltrim(str(convert(smallint ,@tmp) + 1 ))
if len(ltrim(str(convert(smallint ,@tmp) + 1 ))) = 2
set @tmp = '0'+ ltrim(str(convert(smallint ,@tmp) + 1 ))
if len(ltrim(str(cast(@tmp + 1 as smallint)))) = 3
set @tmp = ltrim(str(cast(@tmp + 1 as smallint)))
set @curstr = @curstr + @tmp
set @tmp = @curstr
set @i = @i + 1
end
print '--****** Start===' + @tmp + ' ******'
set @tname=@tmp + 'ABC'
set @tname1=char(96)+'G:\' + @tmp + 'ABC.mdf'+char(96)
set @tname2=char(96)+'G:\' + @tmp + 'ABC_log.LDF'+char(96)
print 'CREATE DATABASE ['+@tname+'] ON
( FILENAME ='+ @tname1+' ),
( FILENAME = '+@tname2+char(96)+' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = '+char(96)+@tname+char(96) +'and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC ['+@tname+'].dbo.sp_changedbowner @loginame=N''sa'', @map=false
GO'

print '--****** Done===' + @tname + ' ******'
print ''
end
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources