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

Utpal Maity

Answers


Something like this would help you. Just an example:

TABLE SCHEMA:

create table person
(
  id int identity(1,1),
  address varchar(50)
)
GO

SCRIPT: (partial)

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


Need Your Help

Jersey 2.8, grizzly, SSE: what jar files do i have to download

java rest jersey-2.0 grizzly

aI am trying to upgrade my jersey-jar files from 1x to 2.8 version. To download them, everyone is pointing here. Obviously, i can download and import all of them but i believe that 5-6 should be

Check if a facebook user is connected to the application from server side in java

java facebook restfb

My application is written with JSF framework. There is a method in JavaScript API that allows to recognize if a user is connected to the application

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.