SQL - conditionally insert space between fields

Posted on 2012-09-14
Medium Priority
Last Modified: 2012-09-16
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?
Question by:tommym121
LVL 12

Expert Comment

ID: 38401017
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.
LVL 25

Assisted Solution

lwadwell earned 1000 total points
ID: 38401063
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


Expert Comment

ID: 38401103
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.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Author Comment

ID: 38401476

How can I get rid of non print character from a column?

Accepted Solution

sameer_goyal earned 1000 total points
ID: 38402578

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.

Author Closing Comment

ID: 38403069

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question