Concat two field in sqlserver 2005

Question

0.00/5 (No votes)

See more:

I am trying to concat follow column in sql.
select(username + ''+ usertype) as ColumnZ from tbluser

after concat these record come.

VB

avinashAdmin
manojAdmin
raghuAdmin
anuAdmin

But i want in space will shown userfirstname and userlastname in column z field result
i want result like:

VB

avinash Admin
manoj Admin
raghu Admin
anu Admin

Thanks.

Posted 10-Dec-10 20:06pm

balongi

Updated 10-Dec-10 20:56pm

v4

Add a Solution

Comments

Abdul Quader Mamun 11-Dec-10 2:49am

Fix spelling in the question title.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abdul Quader Mamun · Accepted Answer · 2010-12-10T20:16:00

Solution 2

Thank you for your question. Use bellow code.

SQL

select(ISNULL(username,'') + ' ' + ISNULL(usertype,'')) as ColumnZ from tbluser

Thanks,
Mamun

Posted 10-Dec-10 20:16pm

Abdul Quader Mamun

Comments

balongi 11-Dec-10 2:22am

what is the use o ISNULL here ?

Abdul Quader Mamun 11-Dec-10 2:44am

In SQL null will not concatenate with string. And it will produce null. To avoid this should check null. If null find then it will replace with empty string. So there will be no problem if any of the field null.

balongi 11-Dec-10 2:53am

Sir i am using this query in asp.net. The result is displayed in datalist if i search for ('manoj admin'), but if user added two spaces between them then datalist dosen't so the record. i need user can supply as manay spaces but the result of query should be generated.

Abdul Quader Mamun 11-Dec-10 3:10am

Use trim function which under String class to reduce spaces between them.

balongi 11-Dec-10 3:23am

i beginner for this can you suggest how i use trim function under string class

Abdul Quader Mamun 11-Dec-10 3:45am

Create two string variables one for user name and another for user type like String strUserName and String strUserType. Then assign your value that is strUserName=UserNameFromDataList and strUserType=UserTypeFromDatalist. Then strUserName.Trim(),strUserType.Trim().

Thanks

RaviRanjanKr · Accepted Answer · 2010-12-10T20:22:00

Solution 3

You can use it by various ways by customizing it like

// 1st way
select (username + ' '+ usertype) as ColumnZ from tbluser

//2nd way
 select(username + ' '+ usertype) 'ColumnZ'  from tbluser

//3rd way
select 'ColumnZ' = (username + ' '+ usertype) from tbluser

:)

Posted 10-Dec-10 20:22pm

RaviRanjanKr

Updated 10-Dec-10 20:31pm

v3

Comments

balongi 11-Dec-10 2:28am

Ok i am using these field in search button. if i search for manoj Admin it give result in datalist. the result shown manoj Admin. but if i added two spaces between them it dosent display the record pleze help

RaviRanjanKr 11-Dec-10 2:34am

of-course it will never give output because if you add one more space it will not match the previous one.
As for Example "manoj Admin" it take only one space between Manoj and Admin but if you add two space it will become like "manoj Admin" which is unmatchable.

balongi 11-Dec-10 2:37am

SO what is the query, for that user can type as many spaces he want.

balongi 11-Dec-10 2:45am

Please suggest me idea!

Goutam Patra · Accepted Answer · 2010-12-10T20:09:00

Solution 1

Use a space

select(username + ' ' + usertype) as ColumnZ from tbluser

[EDIT]
As per your comment I think you are trying to find using (username + ' ' + usertype) = 'avinash Admin'.
Then I would suggest split the input in strings and match individual fields username and usertype. I think that would be better.
[END EDIT]

Posted 10-Dec-10 20:09pm

Goutam Patra

Updated 10-Dec-10 20:34pm

v2

Comments

balongi 11-Dec-10 2:22am

i am using these filed in search button. if i search record for avinash Admin it give avinash admin as result but if i added two spaces between them it dosen't give the output.

Goutam Patra 11-Dec-10 2:31am

It wont. So you are trying to search using (username + ' ' + usertype) = 'avinash Admin'? Take a look at the edited part of my answer.

balongi 11-Dec-10 2:34am

it give result if their is on space between ('avinash Admin') if i write ('avinash Adnub') it not work

balongi 11-Dec-10 2:36am

i am reterving it in asp.net using query suggested by you. user can type as many spaces between them.

balongi 11-Dec-10 2:38am

i dont use split, because i have to work with requirement