September 14, 2010 at 3:28 pm
a.borgeld (9/14/2010)
In one word. Thank you for the lesson Wayne.When its al finished i will come back to this topic.
Kind regards,
André
No problem, I'm glad I can help. If you have more questions, just ask.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 14, 2010 at 8:16 pm
scott.pletcher (9/13/2010)
You should split the alpha and numeric for storage. Don't fall into the trap of feeling you have to store what is displayed / entered.So, create two physical columns, one for the varchar portion of the value and one for the numeric. Then create a computed column that calculates the display value.
For example:
CREATE TABLE #test1 (
c1 varchar(30),
c2 int,
c3 AS c1 + '_' + CAST(c2 AS varchar(10))
)
INSERT INTO #test1 VALUES('Unknown', 200)
SELECT c1, c2, c3
FROM #test1
Once the alpha and numeric are split, it's easy to increment the numeric value.
For the existing table, you could create new column names and make the existing column the computed column ... except for any place where the value is INSERTed / UPDATEd. INSERTs / UPDATEs would have to done against the physical columns, of course, not the computed column.
Uh-huh... how are you selecting the next number and what are you doing to prevent duplication and not just reject it using a unique column... I mean prevent it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2010 at 8:40 pm
WayneS (9/14/2010)
a.borgeld (9/14/2010)
BCP is very cool.Guys with your information and information from Ray Wong i created a function with a loop.
This works.
But i told you about the table who needs to update the Stafnr column, increment it.
The case now is that maybe i get 2 stafmembers a day.
The update wants to update Unknown_200 to Unknow_201, but now there are two records empty so he tries to update the 2 new staff members with Unknown_201. While i want to get a Unknow_201 and after that a Unknown_202, you get two Unknown_201's.
Has anybody got any solution for this.
Sure, just modify what I did earlier:
-- test dataDECLARE @test-2 TABLE (RowID INT IDENTITY, Staff_nbr varchar(20));
INSERT INTO @test-2
SELECT '' UNION ALL -- will need to be updated with the next number
SELECT '' UNION ALL -- will need to be updated with the next number
SELECT '' UNION ALL -- will need to be updated with the next number
SELECT 'Unknown_200'
-- un-remark the following line to work with 4 digit numbers
--UNION ALL SELECT 'Unknown_2000'
-- declare variable to store the current max value
DECLARE @max-2 INT;
-- get the current max value
;WITH CTE (staff_nbr) AS
(
SELECT convert(int, substring(Staff_nbr, charindex('_', Staff_nbr)+1, 20))
FROM @test-2
)
SELECT @max-2 = max(staff_nbr)
FROM CTE;
WITH CTE AS
( -- get just the rows with no staff_nbr.
-- also get an ascending row_number
SELECT RowID,
Staff_Nbr,
RN = ROW_NUMBER() OVER (ORDER BY RowID)
FROM @test-2
WHERE Staff_nbr = ''
)
-- update those missing rows with the max + row number
UPDATE CTE
SET Staff_nbr = 'Unknown_' + CONVERT(varchar(10), @max-2 + RN);
-- show what we've got
SELECT *
FROM @test-2;
I may be missing it but it doesn't look like there's anything that will prevent someone from using the max number you get in the first CTE before you use it. Adding an explicit transaction here would make deadlock heaven. You have to combine the determination of @max-2 with the update in a single query to keep the value being stored in @max-2 from being used before you use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2010 at 10:10 pm
Jeff, good thing to think about. In my case i'm the only one who use this.
September 15, 2010 at 12:09 am
I have yet another alternative. Not only would I split the numeric and the alphanumeric part of the unknown numbers into separate columns, I would suggest also splitting the unknown numbers from the known numbers. i.e. I would suggest to make stafNr a calculated column. Calculated from the KnownNr -if that is available- and the UnknownNr otherwise. The Unknown number is constructed from 2 columns itself -as was suggested-, a prefix plus a number column. The number column can best be implemented as an identity column or (when that is not possible, for example because the database is replicated) it may be determined using a 'next available'-algorithm.
An example:
use tempdb
go
create table dbo.Test (
SomeID uniqueidentifier not null default newsequentialid(),
Name varchar(100) not null,
StaffNr as (isnull(KnownNr,UnknownNrPrefix + right(replicate('0',5) + convert(varchar(36), UnknownNrNumeric),5))),
KnownNr varchar(30) null,
UnknownNrPrefix varchar(25) not null default 'Unknown_',
UnknownNrNumeric numeric(5,0) not null identity(1,1),
primary key (someID),
);
insert dbo.Test(Name)
values('Bond, James')
select StaffNr, Name from dbo.Test
update t
set
KnownNr = 'Agent 007'
from dbo.Test t
where name = 'Bond, James'
select StaffNr, Name from dbo.Test
output is:
(1 row(s) affected)
StaffNr Name
------------------------------ ----------------------------------------------------------------------------------------------------
Unknown_00001 Bond, James
(1 row(s) affected)
(1 row(s) affected)
StaffNr Name
------------------------------ ----------------------------------------------------------------------------------------------------
Agent 007 Bond, James
(1 row(s) affected)
September 15, 2010 at 7:16 am
Jeff Moden (9/14/2010)
I may be missing it but it doesn't look like there's anything that will prevent someone from using the max number you get in the first CTE before you use it. Adding an explicit transaction here would make deadlock heaven. You have to combine the determination of @max-2 with the update in a single query to keep the value being stored in @max-2 from being used before you use it.
You're right, I didn't think about that. I guess I need to start thinking differently about this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 8:50 am
WayneS (9/15/2010)
Jeff Moden (9/14/2010)
I may be missing it but it doesn't look like there's anything that will prevent someone from using the max number you get in the first CTE before you use it. Adding an explicit transaction here would make deadlock heaven. You have to combine the determination of @max-2 with the update in a single query to keep the value being stored in @max-2 from being used before you use it.You're right, I didn't think about that. I guess I need to start thinking differently about this.
Thanks for the feedback, Wayne.
The big problem here (IMHO) is if someone who needs it to work in an environment with more than one person using it finds it and uses the code. Hopefully, they'll see my warning about any of these methods.
I also want people to know what a bad idea it is to do numbering this way. It not only makes coding difficult, it can open the door on a major source of deadlocks or duplicated numbers depending on how it is coded. It violates first normal form because the column is used for more than one thing (identifies what the entry is AND how many there are) and seems to stipulate the reuse of identifiers which is a huge "Bozo-no-no".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2010 at 12:59 pm
But Wayne,
Allthrough the solutions was good for me and i've learned from it.
Goes for Jeff to.
Thank you guys and the goal is that everybody learns, so thats good!
Have a great evening!
Kind regards,
André
September 15, 2010 at 1:34 pm
Do both CTEs in the same statement with the UPDATE; you can have more than one CTE in a statement. That way it will all implicitly be in the same transaction, even if you don't explicitly start a trans.
You could conceivably hit deadlock issues, especially if you run multiple of those UPDATEs at one time -- which would be a very bad idea for what you're doing anyway, so you really should avoid that. That UPDATE should be run individually / serially only.
Scott Pletcher, SQL Server MVP 2008-2010
September 15, 2010 at 1:52 pm
scott.pletcher (9/15/2010)
Do both CTEs in the same statement with the UPDATE; you can have more than one CTE in a statement. That way it will all implicitly be in the same transaction, even if you don't explicitly start a trans.You could conceivably hit deadlock issues, especially if you run multiple of those UPDATEs at one time -- which would be a very bad idea for what you're doing anyway, so you really should avoid that. That UPDATE should be run individually / serially only.
If you mean that the UPDATE should be executed as a single transaction and that the UPDATE does NOT live withint the confines of an explicit transaction that includes other transactions, then I absolutely agree.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 21, 2010 at 7:51 am
Solution i use, Ray Wong published:
create function [StringToArray]
(@str varchar(255),
@delimchar(1),
@token tinyint)
returns varchar (255)
as
begin
declare @start tinyint,
@end tinyint,
@loopcnt tinyint
set@end =0
set@loopcnt=0
set@delim=substring(@delim,1,1)
-- loop naar een specifiek teken
while (@loopcnt < @token) begin
set @start = @end + 1
set @loopcnt = @loopcnt + 1
set @end = charindex(@delim,@str+@delim,@start)
if @end = 0 break
end
if @end = 0
set @STR = null
else
set @STR = substring(@str, @start, @end-@start)
return @STR
end
update [Table] set [StaffNr] = (
select 'UNKNOWN_'+convert(varchar(4),max(convert(int,dbo.StringToArray(StaffNr, '_', 2)))+1)
fromdbo.[Table] where StaffNr like '%unknown%')
where [StaffNr] = ''
/*
Testselect dbo.StringToArray('this string', ' ', 1) --> 'this'
select dbo.StringToArray('this string', ' ', 2) --> 'string'
select dbo.StringToArray('this string', ' ', 3) --> NULL
select dbo.StringToArray('this string', ' ', 2) --> ''
select dbo.StringToArray('UNKNOWN_53', '_', 2) --> result= '53'
select convert(int,dbo.StringToArray('onbekend_503', '_', 2))+1 --> result= 504
select 'UNKNOWN_'+convert(varchar(4),convert(int,dbo.StringToArray('UNKNOWN_503', '_', 2))+1) --> result = UNKNOWN_504
*/
for the update i use the solution of wayne.
And i tested it myself, to learn from it.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply