Select query get autoname - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select query get autoname

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-07-16 : 16:23:37

I pass the name via sp, to table.

if that name exists then just add 1 to the end of teh name, if 1 exists then use 2 like that increment by one.

Can you please tell me how to do that via sql select.

i pass Name via SP using this parameter: @UName

Select Uname from Tab_Unames where Uname =@Uname
if exists then show same uname with 1 at the end.
if 1 edxists show 2 at end like that which ever the numberic is free.

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-07-17 : 08:37:28

[code]SELECT TOP 1 LEFT(Uname,PATINDEX('%[0-9]%',Uname)-1) + CAST(ISNULL(NULLIF(REPLACE(Uname,@Uname,''),''),0) + 1 AS varchar(5))
FROM Table
WHERE Uname LIKE @Uname + '[1-9]%'
ORDER BY ISNULL(NULLIF(REPLACE(Uname,@Uname,''),''),0) DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-07-17 : 08:57:33

you can check for the existence of the name in your sp and accordingly insert/update the table.

Here is what i have done...

create table name_list
(id int identity not null,
name varchar(50) not null,
occurence int)
**********************************************************************************************

create proc AddName(@name varchar)
as
begin
declare @inc int
set @inc = 1
declare @occur int
select @occur = occurence from name_list where name = @name

if @name in (select name from name_list)
begin
set @occur = @occur + @inc
insert into name_list(name,occurence) values (@name,@occur)
--update name_list set occurence = @occur where name = @name
set @inc = @inc + 1
end

else
begin
insert into name_list(name,occurence) values(@name,1) -- (assuming you will give 1 as default for your fresh entry.)
end

end
**********************************************************************************************

mrm23
Posting Yak Master

198 Posts

Posted - 2010-07-17 : 09:00:27

Sorry, i was in a hurry i think... i didnt notice you needed only a select statement.
For your requirement, visakh's answer is correct.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-07-17 : 10:59:40

Thank you visakh, but i am having problem if there are usernames which has the last right side numbers more than 10.
i have test user accts upto 43, but when i fire this query it is checking only single digits i bilieve.

i have user like these in db table which has right side numbers : 3, 4, 6, 9, 11, 14, 17 like random.
is it possible to check if there is a number on the right side end.

All i am trying to do is increment by 1.

this is for function to create auto users, where i should be able to give the username & pwd automatically via system.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-19 : 13:12:22

Use "Cast (.. as Integer ) in Visakh's solution.

SELECT TOP 1 LEFT(Uname,PATINDEX('%[0-9]%',Uname)-1) + CAST(ISNULL(NULLIF(REPLACE(Uname,@Uname,''),''),0) + 1 AS varchar(5))
FROM Table
WHERE Uname LIKE @Uname + '[1-9]%'
ORDER BY Cast(ISNULL(NULLIF(REPLACE(Uname,@Uname,''),''),0) as Int) DESC

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources