concatenating columns

Question

concatenating columns

Prema sambandam

SSCommitted

Points: 1825
More actions
August 25, 2010 at 1:24 pm

#239475

I have first name and last name in my table. I have to concatenate these two columns and update in other column. But if name appears more than once I have to concatenate 1 to that.
eg
Firstname LastName url
David Glance DavidGlance
David Glance Davidglance01
David Glance DavidGlance02
John Smith JohnSmith
How can I achieve this?
Prema

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

So you already have the names in the table and an empty 3rd column, which you wish you populate with the full name plus an incremented number if the name is not unique?

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 2

This should start you off 🙂

--DROP TABLE #Test

CREATE TABLE #Test

( FirstName VARCHAR(100),

LastName VARCHAR(100),

URL VARCHAR(200))

INSERT INTO #Test (FirstName,LastName) VALUES ('Jim','Bob')

INSERT INTO #Test (FirstName,LastName) VALUES ('David','Glance')

INSERT INTO #Test (FirstName,LastName) VALUES ('Keith','Smith')

INSERT INTO #Test (FirstName,LastName) VALUES ('Lucciano','Becchio')

UPDATE #Test SET URL = fst + ' ' + lst + ' ' + CONVERT(VARCHAR(10),cnt)

FROM (SELECT FirstName AS fst, LastName AS lst, COUNT(*) - 1 AS cnt

FROM #Test

GROUP BY FirstName, LastName) upd

WHERE FirstName = fst AND LastName = lst

SELECT * FROM #Test

Cheers,

Jim.

SQL SERVER Central Forum Etiquette[/url]

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

I was going a different way to make it constantly updating with a Trigger, so he wouldn't have to constantly run it after every update..

--DROP TABLE TEST

GO

CREATE TABLE test (

id int identity(1,1),

FN varchar(50),

LN varchar(50),

URL varchar(105)

)

GO

CREATE TRIGGER CreateURLName on dbo.Test AFTER INSERT

AS

BEGIN

DECLARE @cnt int,

@currid int,

@fn varchar(50),

@ln varchar(50),

@url varchar(105)

SET @currid = (SELECT max(id) FROM dbo.test)

SET @cnt = (SELECT COUNT(*) FROM dbo.test tINNER JOIN inserted i ON (t.fn = i.fn AND t.ln = i.ln))

SET @fn = (SELECT fn FROM inserted)

SET @ln = (SELECT ln FROM inserted)

IF @cnt > 1

BEGIN

UPDATE test

SET URL = t.FN + t.LN + cast(@cnt-1 as varchar) FROM test t WHERE ID = @currid

END

IF @cnt = 1

BEGIN

UPDATE dbo.Test

SET URL = FN + LN FROM test t WHERE ID = @currid

END

GO

INSERT INTO TEST (fn, ln)

SELECT 'Dave','Smith'

INSERT INTO TEST (fn, ln)

SELECT 'Dave','Smith'

INSERT INTO TEST (fn, ln)

SELECT 'Dave','Smith'

INSERT INTO TEST (fn, ln)

SELECT 'Dave','Smith'

INSERT INTO TEST (fn, ln)

SELECT 'Doug','Smith'

INSERT INTO TEST (fn, ln)

SELECT 'Doug','Smith'

INSERT INTO TEST (fn, ln)

SELECT 'Doug','Smith'

SELECT * FROM test

edit: changed the code so the second entry starts concatenates 1 instead of 2

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

Does this give you what you're looking for?

IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t

SELECT 'David' AS Firstname, 'Glance' AS Lastname into #t UNION ALL

SELECT 'David', 'Glance' UNION ALL

SELECT 'John', 'Smith'

;WITH cte AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY Firstname, Lastname ORDER BY (SELECT 0)) - 1 AS ROW,

Firstname,

Lastname

FROM #t

)

SELECT Firstname,

Lastname,

Firstname + Lastname + CASE WHEN ROW = 0 THEN '' ELSE '0' + CONVERT(VARCHAR(10), ROW) END

FROM cte

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 5

As an UPDATE

IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t

CREATE TABLE #t

(Firstname VARCHAR(100),

Lastname VARCHAR(100),

url VARCHAR(200))

INSERT #t (Firstname, Lastname)

SELECT 'David', 'Glance' UNION ALL

SELECT 'John', 'Smith'

;WITH cte AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY Firstname, Lastname ORDER BY (SELECT 0)) - 1 AS ROW,

Firstname,

Lastname,

url

FROM #t

)

UPDATE cte SET url = Firstname + Lastname + CASE WHEN ROW = 0 THEN '' ELSE '0' + CONVERT(VARCHAR(10), ROW) END

SELECT Firstname, Lastname, url

FROM #t

Prema sambandam SSCommitted Points: 1825 More actions · Answer 6

Thanks guys. it is working. is there any to automate so that every time new row added it will update by itself

WayneS SSC Guru Points: 95409 More actions · Answer 7

Prema sambandam (8/25/2010)
Thanks guys. it is working. is there any to automate so that every time new row added it will update by itself

Put it into an INSERT trigger.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Derrick Smith SSChampion Points: 10833 More actions · Answer 8

Prema sambandam (8/25/2010)
Thanks guys. it is working. is there any to automate so that every time new row added it will update by itself

Yes, the trigger I posted will do this.

TakeITeasy SSC Enthusiast Points: 181 More actions · Answer 9

Hi,

Create INSERT Trigger on the table and put previous script.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 10

Prema sambandam (8/25/2010)
I have first name and last name in my table. I have to concatenate these two columns and update in other column. But if name appears more than once I have to concatenate 1 to that.
eg
Firstname LastName url
David Glance DavidGlance
David Glance Davidglance01
David Glance DavidGlance02
John Smith JohnSmith
How can I achieve this?
Prema

Gosh this is a bad idea. What happens if you have more than 100 instances of the same name?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)