INTERNET APPLICATION DEVELOPMENT
MID MARKET ERP DEVELOPMENT
by Sheila Zhang
I recently reviewed SQL character functions in SQL 2012. One thing I learned is that these functions also work in SQL 2008.
Commonly, I need to combine several string type fields into one in your select statement. So, I may write something like this:
“SELECT empid, country, region, city, country + COALESCE( N',' + region, N'') + N',' + city AS location FROM HR.Employees”
Here is what I receive:
It is correct that the addresses in the UK don’t have a region. You can use CONCAT() to skip the NULL. Try:
“SELECT empid, country, region, city, CONCAT(country, N',' + region, N',' + city) AS the location FROM HR.Employees”
Here is what you receive:
If you got a full name field in your database, CHARINDEX() is an option to get the salutation, first name and last name.
“SELECT namectac as fullname, left(namectac,charindex('.',namectac)) as salutation FROM ARCUS WHERE namectac<>''”
CHARINDEX() supports the third parameter to set the starting position to find your character.
COALESCE() is recommended to use instead of ISNULL(). After you see the results below you will stick with COALESCE().
COALESCE() represents the current data type of the field. ISNULL() represents the replacement data type for the field.
If you have any questions on SQL character functions, please let me know.