• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

SQL JOIN?

How do I take data from one field and join it with data from another field, plus provide some addtional text around that data. So as an example:

TableUserName                 TableUserNumber                  

Joel                                     555-1212
John                                    555-1213
Mary                                    555-1214


The result needs to look like the below while adding the following characters, "=" and "!"

Joel=555-1212!
John=555-1213!
Mary=555-1214!

Joel
0
Joel_Sisko
Asked:
Joel_Sisko
3 Solutions
 
Patrick MatthewsCommented:
SELECT TableUserName + '=' + TableUserNumber + '!'
FROM SomeTable
0
 
mankowitzCommented:
Plus only adds numbers. You need
SELECT CONCAT( TableUserName , '=' , TableUserNumber , '!')
FROM SomeTable
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
>Plus only adds numbers

er - no it doesn't. in T-SQL, it is the concatenation operator.
beware NULLs though, as by default, where a NULL value is present anywhere in the items being concatenated, this will result in a NULL result.

2 options to avoid this pitfall:

1. use SET CONCAT_NULL_YIELDS_NULL OFF for the session (and turn it back on after your SELECT

2. Use ISNULL to explicitly return a value
eg SELECT ISNULL(TableUserName,'') + '=' + ISNULL(TableUserNumber ,'') + '!'
FROM SomeTable
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Joel_SiskoAuthor Commented:
gbshaaq,

Option 2 of your response (ISNULL), if I understand you correctly, that if there is a "null" in a field the SQL statement will not return anything. So in the example below:

TableUserName                 TableUserNumber                  

Joel                                     <null>
John                                    555-1213
Mary                                    555-1214

The result would be:

John=555-1213!
Mary=555-1214!





0
 
Patrick MatthewsCommented:
The result would be:

<null>
John=555-1213!
Mary=555-1214!
0
 
mankowitzCommented:
>>Plus only adds numbers

>er - no it doesn't. in T-SQL, it is the concatenation operator.

oops. Sorry, I thought i was in the mysql group. My bad.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now