December 16, 2005 at 3:30 am
hi all
i am having problem in finding the max value of name in my views table
i have names stored in my table as view1,view2 etc, so what i wanted to do is i want to find the next max value of the name which obviously 3 so my new name will be view3
the problem is if some one already created a name as 'view of john' then i am running in to problem by using this statement
SELECT @viewcount=ISNULL(max(CAST(SUBSTRING(Name,5,len(Name)) AS Integer)),0)+1
FROM Views
WHERE Name LIKE 'View%'
SET @ViewName='View'+cast(@viewcount as varchar(8))
i hope any genius can guide me in solving the problem.
thanks
December 16, 2005 at 3:50 am
SELECT @viewname= 'View'+CAST(MAX(dt.value)+1 AS VARCHAR(50))
FROM (
SELECT CAST(SUBSTRING(Name,5,len(Name)) AS Integer) as value
FROM Views
WHERE Name LIKE 'View%'
) as dt
December 16, 2005 at 3:59 am
thanks for the reply david
but it gives problem when you have names in the table
as some thing like 'view of john' it gives error as converting varchar to int
i have created the table with some sample data so you can have a look at it
create table Views(Name varchar(50))
insert into Views(Name)
values('View1')
insert into Views(Name)
values('View2')
insert into Views(Name)
values('View of John')
declare @viewname varchar(50)
SELECT @viewname= 'View'+CAST(MAX(dt.value)+1 AS VARCHAR(50))
FROM (
SELECT CAST(SUBSTRING(Name,5,len(Name)) AS Integer) as value
FROM Views
WHERE Name LIKE 'View%'
) as dt
select @viewname
December 16, 2005 at 12:01 pm
December 17, 2005 at 3:28 am
this did the trick david.infact i tried with ISNUMERIC function and it did the trick too..
thanks very much..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply