SQL - conditionally insert space between fields

Posted on 2012-09-14
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

    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

    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

    LVL 5

    Expert Comment

    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.

    Author Comment


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

    Accepted Solution


    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now