Another easier way to concat string

Question

Post reply

Another easier way to concat string

Frances L

SSCarpal Tunnel

Points: 4727
More actions
March 13, 2006 at 12:28 pm

#93441

Any easier way to complish that .

select [Last Name] as [Name] from e where [First Name] is null
select [Last Name] + ', ' + [First Name] as [Name] from e where [First Name] is not null

Thanks.

Frances

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

I don't quite understand what you are looking for....you can only concatenate by adding two or more items.

If you don't want the WHERE clause, try this:

SELECT ISNULL([LastName], 'Lname Unknown') + ' , ' + ISNULL([FirstName], 'Fname Unknown')

-SQLBill

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

If I have two fields I want cancatenate by adding lastname, space firstname.

ABC LastName

EFC

I want to my outcome as LastName, ABC and EFC since the EFC does not have last name input .

Thanks.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

SELECT COALESCE([Last Name] + ', ' + [First Name], [Last Name], [First Name]) as [Name]

Far away is close at hand in the images of elsewhere.
Anon.

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 4

Thanks. It is cool. I know this function. but I do not know why Microsft come out as CoALESCE as Returns the first nonnull expression among its arguments. while I tried to use, I do not know how to spell. Any one can explain how they remember this word.

Darwin Hatheway SSC Veteran Points: 242 More actions · Answer 5

FYI - I'm fairly certain COALESCE is in the ANSI standard syntax, which is why I'd recommend it.

select coalesce(null,'x') returns an 'x' on Teradata. However, nothing is simple on DB2.

select coalesce(null,'x')

coughs up an error message that "the use of the reserved word 'FETCH' is not valid..." DB2 (MVS) apparently always needs a from clause, which is why there's a SYSDUMMY1 table on our system (may be installation dependent) but then:

select coalesce(null,'x') from sysibm.sysdummy1

also fails with "Null is not valid in the context used..." But:

select coalesce('x','a') from sysibm.sysdummy1

does return 'x' and I figure coalesce is supported.

Back to MS-SQL Server... There's also a connection setting:

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

which will probably allow the behavior you seek. I think that's MS SQL Server-specific, so I'm not sure I'd recommend or use it myself.

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 6

SET CONCAT_NULL_YIELDS_NULL { ON | OFF } did not work in my case since I need to make the decision according to the value null or not.