SQL - conditionally insert space between fields

I have two fields that I would like to combine to form a name.  I would like to have a space in between. There should be a between the two fields if both fields has a valid string.  There should be no space before and after the combined string (Name).
How do I structure it?
Who is Participating?
sameer_goyalConnect With a Mentor Commented:

with non-print characters, do you mean null and spaces or are there others that are of concern?

For space or blanks appearing either to the right or left of each column, firstname and lastname, they will be taken care of using ltrim(rtrim clause used in my query above.

However, if you want to remove the space or blanks in between the characters in either columns, you do something like this

select replace(firstname,' ', '') + ' ' + replace(lastname,' ', '') from tablename

I am using the replace sql function to replace any occurrence of space or a blank character anywhere in either column value with a non-blank character.

I am, however, not checking for a null, since, in case of a null, it should output null and not just a blank character.

Let me know if this works for you. Also, if there are other characters that you are concerned about, I will like to know them and i am sure I can provide a solution for them.
If it's a valid string or not will be determined by the data type of the column (I'm assuming varchar, nvarchar, etc here)

You can combine them with a space in between by stating:

select column1 + ' ' + column2

from ....

If you can post some data examples of what you'd consider  valid string, and what you wouldn't, there may be another way to write the condition.
lwadwellConnect With a Mentor Commented:
do you mean something like:
;with sample_data as (
select 'some' as str1, 'value' as str2 union all
select 'another', null union all 
select null, 'lastone')
-- example below
-- ltrim and rtrim to remove leading and trailing spaces
-- isnull to handle nulls
select ltrim(rtrim(isnull(str1,'')+' '+isnull(str2,''))) as concat_str
  from sample_data

Open in new window

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

select ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(lastname)) from tablename

if your column datatype is varchar, char, or any other variant of these, then you can't prevent alphanumeric as well as special characters from entering the columns and they too will be considered as character strings.
tommym121Author Commented:

How can I get rid of non print character from a column?
tommym121Author Commented:
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.

All Courses

From novice to tech pro — start learning today.