SQL Server : concat values

Question

I have 3 columns SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER and SPRTELE_PHONE_EXT - prefix, the number and ext.

I have created a case where I want to display only the SPRTELE_PHONE_NUMBER if SPRTELE_PHONE_AREA and SPRTELE_PHONE_EXT are null. Else if they are not null, the output will be "(prefix)number.ext" with parentheses and dot.

Here is my code:

SELECT
    CASE
        WHEN SPRTELE_PHONE_AREA IS NULL AND SPRTELE_PHONE_EXT IS NULL 
           THEN SPRTELE_PHONE_NUMBER
           ELSE CONCAT('(', SPRTELE_PHONE_AREA, ')', SPRTELE_PHONE_NUMBER, '.', SPRTELE_PHONE_EXT)
    END
FROM 
    vcc.sprtele;

The problem is the output is displayed as the second case on each record: "CONCAT..."

Where is the problem here?

Example: prefix = 650, number = 1234567, ext = 890

if prefix and ext are null -> 1234567
if prefix and ext are not null -> (650)1234567.890

If prefix is null and ext is not null -> 1234567.890 and vice versa

Sample data and desired results would do wonders to communicate what you want to do. — Gordon Linoff, Dec 13 '18 at 19:54
What do you want to do if one of them is NULL and the other is not? — Tab Alleman, Dec 13 '18 at 19:57
Are you sure they are null? Maybe they are just blank? in which case `coalesce(SPRTELE_PHONE_AREA ,'') <> ''` might be what you want. — Dale K, Dec 13 '18 at 19:57
It sounds like you have three options in descending order if any of them are null skip that. Perfect opportunity for a COALESCE(step1, step2, step 3, '') — KeithL, Dec 13 '18 at 20:07
Here's your duplicate: https://stackoverflow.com/questions/2916791/sql-server-string-concatenation-with-null — Tab Alleman, Dec 13 '18 at 20:27

score 1 · Accepted Answer · answered Dec 13 '18 at 20:22

Your query is missing 2 cases:
if prefix is null and ext is not null and
if ext is null and prefix is not null.

SELECT
  CASE
    WHEN SPRTELE_PHONE_AREA IS NULL AND SPRTELE_PHONE_EXT IS NULL THEN SPRTELE_PHONE_NUMBER
    WHEN SPRTELE_PHONE_AREA IS NULL THEN CONCAT(SPRTELE_PHONE_NUMBER, '.', SPRTELE_PHONE_EXT)
    WHEN SPRTELE_PHONE_EXT IS NULL THEN CONCAT('(', SPRTELE_PHONE_AREA, ')', SPRTELE_PHONE_NUMBER)
    ELSE CONCAT('(', SPRTELE_PHONE_AREA, ')', SPRTELE_PHONE_NUMBER, '.', SPRTELE_PHONE_EXT)
  END
FROM vcc.sprtele;

KeithL · Answer 2 · 2018-12-13T20:20:42.373

0

How about this:

COALESCE(CONCAT('(', SPRTELE_PHONE_AREA, ')', SPRTELE_PHONE_NUMBER, '.', SPRTELE_PHONE_EXT)
        ,CONCAT('(', SPRTELE_PHONE_AREA, ')', SPRTELE_PHONE_NUMBER
        ,CONCAT( SPRTELE_PHONE_NUMBER, '.', SPRTELE_PHONE_EXT)
        ,SPRTELE_PHONE_NUMBER,'')

COALESCE takes the first non-null value.

edited Dec 13 '18 at 20:20

answered Dec 13 '18 at 20:14

KeithL

5,348
3
19
25

isn't that what you want? – KeithL Dec 13 '18 at 20:16
i have edited my post with an example on how i want the output to be – mecnism Dec 13 '18 at 20:17
1

@mecnism updated again. You should be able to take it from here – KeithL Dec 13 '18 at 20:21
Thank you very much – mecnism Dec 13 '18 at 20:25

score 0 · Answer 3 · answered Dec 13 '18 at 20:22

Here you go

CREATE TABLE T(
  Prefix VARCHAR(20),
  Num VARCHAR(20),
  Ext VARCHAR(20)
);

INSERT INTO T VALUES
('1', '11', '111'),
(NULL, '22', '222'),
('3', '33', NULL),
(NULL, '44', NULL);

SELECT CONCAT( IIF(Prefix IS NULL, '', CONCAT('(', Prefix, ')')),
               Num,
               IIF(Ext IS NULL, '', CONCAT('.', Ext))
             ) Results
FROM T

Returns:

+-----------+
|  Results  |
+-----------+
| (1)11.111 |
| 22.222    |
| (3)33     |
| 44        |
+-----------+

score 0 · Answer 4 · answered Dec 13 '18 at 20:22

The key here is to put the format characters (parenthesis and period) concatenated to the field value inside the ISNULL() function:

;WITH Data AS (
    SELECT  1 AS ExampleNumber,
            '555' AS Prefix,
            '1234567' AS PhoneNumber,
            '999' AS Extension
    UNION
    SELECT  2 AS ExampleNumber,
            NULL AS Prefix,
            '1234567' AS PhoneNumber,
            '999' AS Extension
    UNION
    SELECT  3 AS ExampleNumber,
            '555' AS Prefix,
            '1234567' AS PhoneNumber,
            NULL AS Extension
)

SELECT      ISNULL('(' + d.Prefix + ')', '')
                + ISNULL(d.PhoneNumber, '')
                + ISNULL('.' + d.Extension, '')
FROM        Data d
ORDER BY    d.ExampleNumber

Results are:

(555)1234567.999
1234567.999
(555)1234567

This method can also be used to concatenate, for example, Address1, Address2, and Address3 without gaps, if you concatenate a carriage return + line feed, for multi-line address output.

SQL Server : concat values

4 Answers4