January 15, 2012 at 8:04 am
I have a member data base table "MEMBER_ACCT" which contains two text columns, "mbr_1st_nam" and "mbr_lst_nam". Both fields contain text data and I'm trying to concatenate them using the following:
SELECT MEMBER_ACCT.mbr_1st_nam +' ' + MEMBER_ACCT.mbr_lst_nam AS FullName
FROM MEMBER_ACCT
I'm getting what appears to work, that is, the "FullName" column appears in the result, but the records are all populated with the value "0".
Any assistance would be greatly appreciated.
I'm using SQL SERVER 2008 R2
January 15, 2012 at 9:04 am
Please provide create script for table MEMBER_ACCT and some representative data.
should be easy to solve then
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 15, 2012 at 9:10 am
I'm not sure what you mean by the "create script". The table is in a very large database and contains several hundred thousand records. Sample of the data contained is:
for "mbr_1st_nam" field the records are like:
John
David
Sally
for the "mbr_lst_nam" field the records are like:
Jones
Smith
Johnson
Each of the fields contain text name values representing either the first name or the last name of the members in the table.
January 15, 2012 at 9:19 am
In SSMS....find your table...right click...script table as....create to...new query window
copy and paste results here
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 15, 2012 at 9:41 am
does this work for you
SELECT cast(MEMBER_ACCT.mbr_1st_nam as varchar(50)) +' ' + cast(MEMBER_ACCT.mbr_lst_nam as varchar(50))AS FullName
FROM MEMBER_ACCT
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 15, 2012 at 10:28 am
This also returns data values of '0' in the FullName field.
January 15, 2012 at 10:32 am
OK...so are you able to provide the "create table" script please.?
we will then know what datatypes we are dealing with.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 17, 2012 at 9:06 am
I've verified that both the fields I am trying to "concatenate" are string data......I've also discovered that the + operator does not work for these but the || does work....
Thanks for your assistance....problem solved....
January 17, 2012 at 12:18 pm
I really appreciate the feedback......I'm still very new at all this.....
Do you think you might be able to expand on the differences between columns and fields as they relate to relational databases? It would be very helpful...
January 17, 2012 at 12:44 pm
rbond 51820 (1/17/2012)
I've verified that both the fields I am trying to "concatenate" are string data......I've also discovered that the + operator does not work for these but the || does work....Thanks for your assistance....problem solved....
Are you sure you are using SQL Server? Last I knew sql server did not support || as a concatenation. The concatenation operator in sql server is the plus sign '+'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2012 at 12:52 pm
hmm... Oracle maybe??
http://stackoverflow.com/questions/278189/oracle-string-concatenation-operator
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 17, 2012 at 12:54 pm
That was my guess. Would certainly explain why our help didn't work. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2012 at 1:03 pm
I'm working on SQL SERVER 2008 R2 platform and using Visual Studio 2008 Business Intelligence Development Studio....
Along with SQL Server Reporting Services - Report Manager for the subscription component...
Perhaps I haven't provided all the necessary information concerning my environment, but then again, this is all very new to me...
January 17, 2012 at 1:08 pm
I see...I am by no means an SSRS guru. I can barely spell it. Interesting that the syntax is different for concatenation than in tsql. Either way, glad you were able to resolve your issue. Don't be scared off by being new. This site is a fantastic resource for learning about sql server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2012 at 1:13 pm
Thanks, Sean for your feedback....this is a wonderful place for someone like me and by and large the assistance provided here has always been very well-intentioned.....
Sometimes I just don't know enough to ask all the right questions......
Much appreciated...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply