Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A CONCAT Function For The Rest Of Us Expand / Collapse
Author
Message
Posted Friday, May 18, 2012 1:14 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 26, 2016 2:33 AM
Points: 606, Visits: 364
Comments posted to this topic are about the item A CONCAT Function For The Rest Of Us
Post #1302784
Posted Monday, May 21, 2012 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 27, 2016 5:44 PM
Points: 13, Visits: 291
Why would I use this function rather than simply joining the strings with "+" as it does inside the function. Especially when there are more that two strings being joined, where it looks like it would be more expensive to call a function multiple times.
Post #1303595
Posted Monday, May 21, 2012 3:42 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 26, 2016 2:33 AM
Points: 606, Visits: 364
Hi Steve

Nobody is making you use the function. But to give one quick example where it could be useful. Say you have two string variables, and one of them is NULL. If you simply use + to add these together, you'll receive NULL - i.e. the valid string value will be lost. Using this CONCAT function, the string will be returned.

Example:

DECLARE @S1 VARCHAR(10)
DECLARE @S2 VARCHAR(10) = 'Hello'

SELECT dbo.CONCAT(@S1, @S2, 1) -- Returns 'Hello'

Regards,
Mike.
Post #1303773
Posted Monday, May 21, 2012 3:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 27, 2016 5:44 PM
Points: 13, Visits: 291
I didn't mean to imply that I was being forced. I was just trying to see if I was missing an aspect that would make it more useful in some places. But what it seems like is that there is no benefit over the tried and true method that most of us have been using for years with either coalesce or isnull.

select COALESCE(@String1,'') + ISNULL(@String2,'')

Post #1303776
Posted Monday, May 21, 2012 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 27, 2016 5:44 PM
Points: 13, Visits: 291
OK, I have found a benefit of the CONCAT function in SQL 2012. it is automatically converting data types to strings and outputting them rather than giving you data type mismatches, and forcing you to convert all the inputs. I can also see how people will consider this lazy and at least in the case of DateTime fields, you would generally convert to a specific format.

but instead of coding :
SELECT 'Current Time ' + CAST(GETDATE() AS VARCHAR(20))
you could use
SELECT CONCAT('Current Time ', GETDATE())
Post #1303778
Posted Wednesday, June 6, 2012 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 6, 2015 8:03 AM
Points: 25, Visits: 45
The SQL Server 2012 CONCAT function accepts two or more string values. The user-defined CONCAT function accepts only two values. It's the "two or more" part that would be valuable.
Post #1311850
Posted Tuesday, May 10, 2016 9:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:30 AM
Points: 7,902, Visits: 768
Thanks for the script.
Post #1784813
Posted Tuesday, May 10, 2016 10:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 26, 2016 2:33 AM
Points: 606, Visits: 364
Glad it was useful!
Post #1784850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse