find next number based on char column

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?

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

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]

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-19 : 11:17:29

Thanks That did the trick!

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....

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.

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-19 : 11:47:19

Sorry. Good luck with it.

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources