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.
Author |
Topic |
mkdlmr
Starting Member
21 Posts |
Posted - 2014-02-22 : 13:30:40
|
Hi All,I have a alphanumeric column that looks something like this:T0001T0003T0004T0005How would I go about identifying T0002 as the missing value in the sequence?Thanks,MK |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-22 : 14:42:05
|
[code]DECLARE @Sample TABLE ( Data CHAR(5) NOT NULL );INSERT @Sample ( Data )VALUES ('T0001'), ('T0003'), ('T0004'), ('T0005');WITH cteSource(Data, rn, info)AS ( SELECT Data, ROW_NUMBER() OVER (ORDER BY Data) AS rn, CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info FROM @Sample)SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]FROM cteSourceWHERE rn <> InfoORDER BY rn;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
mkdlmr
Starting Member
21 Posts |
Posted - 2014-02-22 : 15:59:56
|
Thanks! That seems to do the trick, but I did notice that it cannot pick up a missing lead number. For example:T0002T0003T0004T0005Will not return T0001. Also, is there a way to return the next result if it finds nothing? IE:T0001T0002T0003would return T0004 as the next number? I hope I'm not asking for too much. O:-)Thanks,MK |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-23 : 03:05:52
|
quote: Originally posted by mkdlmr Thanks! That seems to do the trick, but I did notice that it cannot pick up a missing lead number. For example:T0002T0003T0004T0005Will not return T0001. Also, is there a way to return the next result if it finds nothing? IE:T0001T0002T0003would return T0004 as the next number? I hope I'm not asking for too much. O:-)Thanks,MK
the easiest way to implement this is by using a calculated column based on an IDENTITY fieldseehttp://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-24 : 12:31:50
|
quote: Originally posted by mkdlmr Will not return T0001.
Did you even TRY my suggestion?DECLARE @Sample TABLE ( Data CHAR(5) NOT NULL );INSERT @Sample ( Data )VALUES ('T0002'), ('T0003'), ('T0004'), ('T0005');WITH cteSource(Data, rn, info)AS ( SELECT Data, ROW_NUMBER() OVER (ORDER BY Data) AS rn, CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info FROM @Sample)SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]FROM cteSourceWHERE rn <> InfoORDER BY rn; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
mkdlmr
Starting Member
21 Posts |
Posted - 2014-02-24 : 14:31:42
|
Yes, I did try it out, but it did not start at 1 if 1 was missing or increment after the last one. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-25 : 11:33:09
|
That's because the original sample data only reflected the first requirement.If you change the sample data (just the way I did in my second post), you automatically get your solution as per second requirement too. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-25 : 11:41:51
|
Now, look at the magic. The only thing I do is changing the sample data.The actual work being done is intact between test cases.-- First test case (second value missing: answer is T0002)DECLARE @Sample TABLE ( Data CHAR(5) NOT NULL );INSERT @Sample ( Data )VALUES ('T0001'), ('T0003'), ('T0004'), ('T0005');WITH cteSource(Data, rn, info)AS ( SELECT Data, ROW_NUMBER() OVER (ORDER BY Data) AS rn, CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info FROM ( SELECT Data FROM @Sample UNION SELECT 'Z9999' ) AS d)SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]FROM cteSourceWHERE rn <> InfoORDER BY rn;GO-- Second test case (first value missing: answer is T0001)DECLARE @Sample TABLE ( Data CHAR(5) NOT NULL );INSERT @Sample ( Data )VALUES ('T0002'), ('T0003'), ('T0004'), ('T0005');WITH cteSource(Data, rn, info)AS ( SELECT Data, ROW_NUMBER() OVER (ORDER BY Data) AS rn, CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info FROM ( SELECT Data FROM @Sample UNION SELECT 'Z9999' ) AS d)SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]FROM cteSourceWHERE rn <> InfoORDER BY rn;GO-- Third test case (last value missing: answer is T0005)DECLARE @Sample TABLE ( Data CHAR(5) NOT NULL );INSERT @Sample ( Data )VALUES ('T0001'), ('T0002'), ('T0003'), ('T0004');WITH cteSource(Data, rn, info)AS ( SELECT Data, ROW_NUMBER() OVER (ORDER BY Data) AS rn, CAST(SUBSTRING(Data, 2, 4) AS INT) AS Info FROM ( SELECT Data FROM @Sample UNION SELECT 'Z9999' ) AS d)SELECT TOP(1) 'T' + RIGHT('0000' + CAST(rn AS VARCHAR(4)), 4) AS [Missing value]FROM cteSourceWHERE rn <> InfoORDER BY rn;GO Yes, you can say "Thank you" now. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
mkdlmr
Starting Member
21 Posts |
Posted - 2014-02-25 : 12:26:56
|
Thank You! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-02-25 : 12:31:18
|
You're welcome. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|