problem in database

shreyas adikumar

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Malvik Bhavsar6y ago
Accepted 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
0
shreyas adikumar6y ago
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.
0
Malvik Bhavsar6y ago
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
0