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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 find next number based on char column

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-19 : 10:18:23
Hi

I have a table that has 2 columns, textNumber and textNote. The textNumber is a Char(5) value and can have values like 001, A001,B001.

I need to be able to find the next available number based on exsisting values, in this case I would like to find the value 002 since that would be the next "available" number. I know this is a bit weird and hope you guys see what I mean and could help me out.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-19 : 10:33:13
Can textNumber be like AA001, BBB001, etc. with a variable length alphabetic prefix?
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-19 : 11:06:57
No, its Always with one ore none alphabetic character and then numbers
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-19 : 11:12:33
[code]

SELECT MAX(CASE WHEN textNumber LIKE '[A-Z]%' THEN SUBSTRING(textNumber, 2, 4) ELSE textNumber END) + 1
FROM ( --table_name
SELECT '001' AS textNumber UNION ALL
SELECT 'A001' UNION ALL
SELECT 'B001'
) AS test_data

[/code]
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-19 : 11:17:29
Thanks That did the trick!
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-19 : 11:29:34
Hmm, but it doesnt seem to work for values that don't start with a alphabetic charachter....
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-19 : 11:33:02
What do you mean "won't work"? If anything other than the first character is non-numeric, it won't work, because of invalid data conversion. But, if what you stated:
quote:
its Always with one ore none alphabetic character and then numbers

is actually true, it should work fine.
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-19 : 11:38:26
Well it doesn't, I used it to find the next available number which became 09111, but when I tried this once more it return 9111 which should have returned 9112
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-19 : 11:47:19
Sorry. Good luck with it.
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-19 : 11:52:14
Your code works fine like this..



SELECT MAX(CASE WHEN textNumber LIKE '[A-Z]%' THEN SUBSTRING(textNumber, 2, 4) ELSE textNumber END) + 1
FROM ( --table_name
SELECT '09111' AS textNumber UNION ALL
SELECT 'A09111' UNION ALL
SELECT 'B09111'
) AS test_data



But it does not work when I try like this...



SELECT MAX(CASE WHEN textNumber LIKE '[A-Z]%' THEN SUBSTRING(textNumber, 2, 4) ELSE textNumber END) + 1
FROM ( --table_name
SELECT '09110' AS textNumber UNION ALL
SELECT 'A09110' UNION ALL
SELECT 'D09111' UNION ALL
SELECT 'B09110'
) AS test_data





Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-19 : 14:23:18
Ah, so it's not "char(5)", you can have more digits. We need to cast to int anyway because of the leading zeros. The cast to int will work fine as long as there are no nonnumeric chars in the value:

SELECT MAX(CAST(CASE WHEN textNumber LIKE '[A-Z]%' THEN SUBSTRING(textNumber, 2, 10) ELSE textNumber END AS int)) + 1
FROM ( --table_name
SELECT '09110' AS textNumber UNION ALL
SELECT 'A09110' UNION ALL
SELECT 'D09111' UNION ALL
SELECT 'B09110'
) AS test_data

Go to Top of Page
   

- Advertisement -