SQL Server Performance Forum – Threads Archive
ignore zero on left side
I am trying to generate a unigue code for every user in my table. This code sholud look like:"S" & Current Year & Unique Number.
And this Unique Number consists of 5 digitis such that:
First —> 00001 —-> SXX00001
Second —> 00002 —-> SXX00002
Third —> 00003 —-> SXX00003
Fourth —> 00004 —-> SXX00004
and so on…..
What I do is that I select the last 5 digits of the code
and convert it to integer and and then add 1. It works but the result will be —-> SXX5 and not —-> SXX00005,
meaning it ignores the zeros on the left?
Can any body help me to fix this code such that the new id does not ignore the zeros on the left
My code is:
select @Mv = RIGHT(SID, 5)From SRegisteration
set @Mv = CAST(@Mv AS int) + 1 Thanks Rasha zaki
Web Developer
Cairo, Egypt
Try
select substring(SID, patindex( ‘%[1-9]%’, SID ), 5 ) FROM SRegistration Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Why do you want to do this inside your DB? You’d be far better off creating your string in your app and sending it to the server. What do you want this for? Frank
http://www.insidesql.de
http://www.familienzirkus.de
I have tried the "patindex" solution, but it did not work and when I went to BOL to see how it works I Find its definition as follows:
"Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types" and this is not exactly what I want. I want to:
1 – select the last five digits of the id (the whole id is string but the last 5 digits are numbers e.g: SXX00001)
2 – I must take care to selct max value of thse last digits, meaning if there are Sxx00001 and SXX00014 and SXX00003 then I shoud select the SXX00014
3 – Add 1 to these last 5 digits after converting them to int
4 – here comes the problem the result will be 15 and not 00015
I am sorry to bother you again.
Thanks Rasha zaki
Web Developer
Cairo, Egypt
How about this –
declare @string1 varchar(10)
set @string1 = ‘S0400012’ select left(@string1,3) + right(replicate(‘0’,5) + convert(varchar(5),convert(int,right(@string1,5))+ 1),5) This is oversimplified, since you need to find the highest number in a table. Replace @string with youu field in the statemetn below and try it select top 1 left(@string1,3) + right(replicate(‘0’,5) + convert(varchar(5),convert(int,right(@string1,5))+ 1),5)
from table1
order by convert(int,right(@string1,5)) desc
is your table still subjected to inserts? are you planning to generate this code as you insert new rows? in this case take into consideration that (unless you use high isolation level) you cannot enssure uniqueness using this method.
Hi ChrisFretwell and bambola,
I have tried the your solution ChrisFretwell and the result was as follows:
If The id retrieved from the db is: sy300001 —-> the
new code genereated will be: SY3SY300 which means that the
first three chracters were the ones to be replicated and not
the zeros "0".
Any way I used the following code which solved the problem but I am
not sure it is the easiest code to fix the case: select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration
Set @IdLen = DATALENGTH(@Mv)
Set @Mv =
case
when @IdLen = 1 then ‘0000’ + @Mv
when @IdLen = 2 then ‘000’ + @Mv
when @IdLen = 3 then ’00’ + @Mv
when @IdLen = 4 then ‘0’ + @Mv
else @Mv
end
——————————————–
As for the isolation level, I have not thought of possibility of
missing uniqness before you bambola directed my attention to it
and in fact I do not have any expertise in this,so if you please
recommend me sources to lewarn from it (if you can) Thanks for both of you bambola and ChrisFretwell Ah! can you help me in another code problem, I have posted it
under the name of "Loop through a list box" in this forum
Thank you again Rasha zaki
Web Developer
Cairo, Egypt
Try this one: declare @vstring char(20)
declare @vmax int
set @vstring = ‘sy300001’
set @vmax = cast(substring(@vstring,4,5) as int) select
substring(@vstring,1,3) +
replicate(‘0’,5-len(ltrim(cast(@vmax as char)))) +
cast(@vmax+1 as char) Jon M
Here is what I usually do in situation like this. — this is a little table to hold field description and a seed, in this case ‘user_code’ and 1
create table my_seq1 (my_field varchar(20), my_id int)
insert into my_seq1 select ‘user_code’, 1 — this stored procedure will asign the next id to a variable, than increment the column.
— you will only lock that row of the table, and not the users table to get the next id.
— 2 processes will never get the same id as it is incremented in the same statement create procedure get_next_id
(
@my_field varchar(20)
)
as set nocount on declare @nextid int update my_seq1
set @nextid = my_id = my_id + 1
where @my_field = @my_field if @@rowcount = 0
return -1 return @nextid — this function will format the user code the way you need.
CREATE FUNCTION fn_format_user_code(@id int, @my_date datetime)
RETURNS varchar(10)
AS
BEGIN
return ‘S’ + substring(convert(varchar(4),year(@my_date)), 3, 4) + right(‘00000’ + convert(varchar(5), @id), 5)
END here is the call to all of this. you could wrap it within a stored procedure. declare @new_code int
declare @today datetime
select @today = getdate() exec @new_code = dbo.get_next_id ‘user_code’ <– gets you the next seq number
select dbo.fn_format_user_code(@new_code, @today) <– for mat the new code
Got to run. will try to explain better later.
Cant help you with your other code – its outside of sql. No idea why the replicate wouldnt work, but instead of replicate, all you need to do is replace
select left(@string1,3) + right(replicate(‘0’,5) + convert(varchar(5),convert(int,right(@string1,5))+ 1),5) with select left(@string1,3) + right(‘00000’ + convert(varchar(5),convert(int,right(@string1,5))+ 1),5) All the replicate(‘0’,5) does is create a string of 00000 Chris
You could build a stored procedure or a function along these lines …… DECLARE @NewCode VARCHAR(100), @Initials VARCHAR(10), @CodeLength INT
SET @Initials = ‘SY’
SET @CodeLength = 5 — Get the numerical maximum without the initials.
SELECT @NewCode =
MAX(CONVERT(BIGINT,
RTRIM(SUBSTRING(SRegisteration.SID, LEN(@Initials) + 1, LEN(SRegisteration.SID)))))
FROM SRegisteration
WHERE SID LIKE (@Initials + REPLICATE(‘[0-9]’, LEN(SID) – LEN(@Initials))) — Add 1 to the numerical maximum.
SET @NewCode = CONVERT(INT, ISNULL(@NewCode, 0)) + 1
— Make sure that the code is long enough, and add the initials.
IF LEN(@NewCode) < @CodeLength
SET @NewCode = @Initials + RIGHT(REPLICATE(‘0’, @CodeLength) + @NewCode, @CodeLength)
ELSE
SET @NewCode = @Initials + @NewCode Adriaan
]]>