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

Concatenating "Empty" fields

Hi

I know how to concatenate fields but I am not sure how to achieve what I want, I have tried, replace, rtrim etc but have not managed to achieve the following....


Many thanks


Title, Firstname, Surname
NULL, IT, girl
'', IT, girl
Ms, NULL, girl
Ms, '', girl

TO ACHIEVE THE FOLLOWING.... (i.e. no spaces, blanks or nulls)
IT girl
IT girl
Ms girl
Ms girl

Open in new window

0
ITgirl
Asked:
ITgirl
1 Solution
 
EyalCommented:
select ltrim(isnull(Title,'') + ' ' + isnull(Firstname,'') + ' ' + isnull(Surname,''))
from ...
0
 
jvejskrabCommented:

CREATE TABLE t (
      c1 varchar(50),
      c2 varchar(50),
      c3 varchar(50)
)

INSERT INTO t SELECT NULL, 'IT', 'girl'
INSERT INTO t SELECT '', 'IT', 'girl'
INSERT INTO t SELECT 'Ms', NULL, 'girl'
INSERT INTO t SELECT 'Ms', '', 'girl'

SELECT ISNULL(NULLIF(c1 + ' ', ''),'') + ISNULL(NULLIF(c2 + ' ', ''),'') + ISNULL(c3,'')
FROM t

DROP TABLE t
0
 
ITgirlAuthor Commented:
that's briiliant thank you so much :)
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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