Click here to Skip to main content
14,575,110 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am trying to concat follow column in sql.
select(username + ''+ usertype) as ColumnZ from tbluser

after concat these record come.

avinashAdmin
manojAdmin
raghuAdmin
anuAdmin


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

avinash Admin
manoj Admin
raghu Admin
anu Admin



Thanks.
Posted
Updated 10-Dec-10 20:56pm
v4
Comments
Abdul Quader Mamun 11-Dec-10 2:49am
   
Fix spelling in the question title.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Thank you for your question. Use bellow code.

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


Thanks,
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
Rate this:
Please Sign up or sign in to vote.

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


:)
   
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!
Rate this:
Please Sign up or sign in to vote.

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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100