Inserting data from 2 columns into 1

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)

Inserting data from 2 columns into 1

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2008-09-09 : 18:08:31

Hello,

I would like to take data from 2 columns and combine them into 1 column for quick search purposes. I already have the existing data and the table design is as follows;

tblUser
nameFirst, nameLast, firstlast

id' like to loop through the current records and combine namefirst + namelast (with a space between).

Any help would be greatly appreciated.

cheers
rob

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-09 : 19:36:06

I don't think combining the column values will speed up your searches. Do you have any primary key and/or indexes on this table?
And, generally, you don't want to "loop through" your rows but rather perform the task in a SET with a single statement.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-10 : 03:47:41

Are you trying to do this?

Where (nameFirst+' '+ nameLast like '%'+@name+'%')

Then better usage is

Where (nameFirst=@name or nameLast=@name)

Madhivanan

Failing to plan is Planning to fail

robc
Yak Posting Veteran

60 Posts

Posted - 2008-09-10 : 15:56:17

Thanks for the replies,

TG - I basically wanted to do it so I could search the firstname and lastname easier, by just using 1 keyword. But that seems unneccesary now.

Mad - Yes, I am trying to do that. Your suggestion worked. In the code below I had "AND" rather than "OR". Is coalesce overkill now?

namefirst like '%' + Coalesce(@NameFirst,namefirst) + '%' OR namelast like '%' + Coalesce(@NameFirst,nameFirst) + '%'

Thank you for the help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-11 : 03:25:20

You can also use

Where @name is null or (nameFirst=@name or nameLast=@name)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources