SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: String Functions: Incrementing a Number in a Char
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/20/2000 :  03:10:25  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 11/27/2000 :  20:27:00  Show Profile  Reply with Quote
did you know?

this numbering system is exactly the same format as car registrations in the state of Victoria, here in Australia. Our licence plate looks like this "PMN-304"

:-)

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/29/2000 :  05:08:15  Show Profile  Reply with Quote
Incrementing a char string

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.

1) DECLARE two variables @leftpart (varchar) @rtpart int
2) Put the appropriate value in the parts
a) SET @leftpart = Left('PIT001', 3)
b) SET @rtpart = CONVERT(int, right('PT001', 3))
3) Test the @rtpart to see if it's 999. If it is set it to 000. do not forget to format to fill the leading zeros
4) If the rtpart was 999 then test to see if your chars are 'Z' if so set char to 'A' and increment next char.

Go to Top of Page

tribune
Posting Yak Master

USA
105 Posts

Posted - 12/21/2001 :  00:15:55  Show Profile  Reply with Quote
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 "<p>Incrementing PIT001 = " & IncProductString("PIT001")
Response.Write "<p>Incrementing PIT002 = " & IncProductString("PIT002")
Response.Write "<p>Incrementing PIT999 = " & IncProductString("PIT999")
Response.Write "<p>Incrementing AAZ999 = " & IncProductString("AAZ999")
Response.Write "<p>Incrementing AZZ999 = " & IncProductString("AZZ999")
Response.Write "<p>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
%>

Go to Top of Page

avan
Starting Member

India
1 Posts

Posted - 01/14/2003 :  00:49:11  Show Profile  Reply with Quote
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
Go to Top of Page

rihardh
Constraint Violating Yak Guru

Slovenia
307 Posts

Posted - 01/14/2003 :  02:10:39  Show Profile  Reply with Quote
We had a similar one before it was solved with approx. 7 lines of code.

Search the forum for topics about 14 days old.

Go to Top of Page

gates_micro
Starting Member

29 Posts

Posted - 05/21/2004 :  07:43:46  Show Profile  Reply with Quote
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
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 05/21/2004 :  09:54:08  Show Profile  Reply with Quote
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

Edited by - ehorn on 05/21/2004 09:57:52
Go to Top of Page

gates_micro
Starting Member

29 Posts

Posted - 05/22/2004 :  13:36:58  Show Profile  Reply with Quote
Nice way to do it.

Go to Top of Page

rohit.guru2004
Starting Member

2 Posts

Posted - 07/30/2010 :  04:00:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000