Getting next matching string value from a SQL table
My SQL table have a column name AddressName . I am storing customer address name in that column. And I want to store them in the following format, suppose when I inserted first time a value in that column it stored 'Utpal' in that field next time before inserting any value I want to fetch the next string value to be inserted as 'Utpal2' and will insert that value in the table.Similarly in the next insertion the value should be 'Utpal3'.How to do this task using SQL query please help.
Thanks and Regards
Something like this would help you. Just an example:
create table person ( id int identity(1,1), address varchar(50) ) GO
--insert second record insert into person select top 1 left(address, case patindex('%[0-9]%', address) when 0 then len(address) else patindex('%[0-9]%', address) - 1 end) + convert(varchar(10),convert(numeric(10,0),Coalesce(NULLIF(right(address, case patindex('%[0-9]%', address) when 0 then 0 else len(address) - patindex('%[0-9]%', address) + 1 end ) ,'') ,0) ) + 1 )from person order by id desc GO
Check out SqlFiddle: