| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
tribune
Posting Yak Master
USA
105 Posts |
 Posted - 12/21/2001 : 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 "<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
%>
|
 |
|
|
avan
Starting Member
India
1 Posts |
Posted - 01/14/2003 : 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
Slovenia
307 Posts |
Posted - 01/14/2003 : 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 - 05/21/2004 : 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
Flowing Fount of Yak Knowledge
USA
1629 Posts |
Posted - 05/21/2004 : 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 |
Edited by - ehorn on 05/21/2004 09:57:52 |
|
|
|
gates_micro
Starting Member
29 Posts |
Posted - 05/22/2004 : 13:36:58
|
Nice way to do it.
|
|
|
|
rohit.guru2004
Starting Member
2 Posts |
Posted - 07/30/2010 : 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 |
|
|
| |
Topic |
|
|
|
Article: String Functions: Incrementing a Number in a Char
