problem in database

Question

hi i am writing this query even i though writtten like this

select max([slno)) as slno from Sales

it is not taking vaue more than 9999

please suggest a solution regading this it will be a great help thanks in advance

Malvik Bhavsar · Answer

Dear Shreyas, So you need to split numeric value first. Try something like this DECLARE @temp TABLE ( string NVARCHAR(50) ) INSERT INTO @temp ( string ) VALUES ( 'IT#999' ), ( 'IT#19999' ), ( 'IT#9999' ), ( 'IT#2999' ), ( 'IT#59999' ) select max(cast (num as int )) --Value from ( select subsrt1 as Value,num from ( SELECT subsrt1,LEFT(subsrt, PATINDEX( '%[^0-9]%' , subsrt + 't' ) - 1) as num FROM ( SELECT subsrt1= string ,subsrt = SUBSTRING( string , pos, LEN( string )) FROM ( SELECT string , pos = PATINDEX( '%[0-9]%' , string ) FROM @temp ) d ) t) m)z

shreyas adikumar · Answer

Hi Malivik i have tried because of joining of character and Number IT#9999.I don't know how to do with it please can u guide me regarding that.

Malvik Bhavsar · Answer

Hey Shreyas, First, your query has a syntax error. And I think slno datatype is varchar. right? Correct query : select max(cast(slno as int)) as slno from Sales Check following sample for int & string datatype. DECLARE @Sales TABLE ( slno int ) INSERT INTO @Sales (slno ) VALUES (1), (2), (3), (4444), (55555), (666666), (7777), (9999) select max (slno ) as slno from @Sales DECLARE @Sales TABLE ( slno varchar (50) ) INSERT INTO @Sales (slno ) VALUES (1), (2), (3), (4444), (55555), (666666), (7777), (9999) select max ( cast (slno as int )) as slno from @Sales

problem in database

Answers (3)