1. Case Function
CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ELSE else_result_expression] END
Judging from experience, CASE is the most used function in data conversions, when the output value is based on on one or multiple constraints over the already existing values.
CASE WHEN column1 = 'yes' OR column2 = 'other' THEN 'yes/other' END
Just be careful, when the ELSE is not provided, a null will be returned!
Another important thing, the values returned must have the same base type, dates, strings or numeric values can't be mixed without conversion!
--example 1 CASE WHEN DateDiff(d, DateValue, GetDate())>0 THEN 'past' WHEN DateDiff(d, DateValue, GetDate())=0 THEN 'today' ELSE 'future' END --example 2 CASE WHEN column1 BETWEEN 1 AND 100 THEN '1st range' WHEN column1 BETWEEN 101 AND 1000 THEN '2nd range' ELSE '3rd range' END
--example 3 CASE WHEN column1 IN ('A', 'B', 'C') THEN '1st set' WHEN column1 IN ('C', 'D', 'E') THEN '2nd set' ELSE '3rd set' END
There is a saying for DBAs and not only, "Know your data!". As each statement is checked until comparison operation evaluated True, it is better to arrange the WHEN expression in the descending order of highest occurrence.
2. IsNull Function
IsNull(expression)
It's always a good idea to check for NULL values, they could occur anytime when a default value is not provided for a value.
One of the cases I often found was when joining two tables when one of the values from JOIN was NULL.
-- example for string values SELECT * FROM table1 A LEFT JOIN table2 B ON A.column1 = B.column1 AND IsNull(A.column2, ' ') = IsNull(B.column2, ' ') WHERE B.column1 IS NULL -- example for numeric values SELECT * FROM table1 A LEFT JOIN table2 B ON A.column1 = B.column1 AND IsNull(A.column2, 0) = IsNull(B.column2, 0) WHERE B.column1 IS NULL
The ' ' and 0 values should be used when they aren't found in the list of possible values or there is no impact on the result.
Another used value for IsNull function is 'N/A', to point out the missing value.
3. Coalesce Function
COALESCE ( expression [ ,...n ] )
There are cases when is needed to take the first not null value from a list of columns, could be dates or any type of other value.
Instead of writting something like
IsNull(Date1, IsNull(Date2, IsNull(Date3, GetDate())
it's much easier to write
COALESCE(Date1, Date2, Date3, GetDate())
-- example coalesce SELECT * FROM table1 WHERE COALESCE(Column1, Column2, Column3) IS NOT NULL -- example IsNull alternative SELECT * FROM table1 WHERE Column1 IS NOT NULL OR Column2 IS NOT NULL OR Column3 IS NOT NULL
4. NullIf Function
NullIf ( expression , expression )
NullIf function is handy especially when is not needed a certain value in output, for example 0 or the .
-- example numeric values SELECT column1 , NullIf(column2, 0) column2 FROM table_name -- example string or date values SELECT column1 , NullIf(column2, '') column2 FROM table_name
When multiple values are not needed, it's easier maybe to write
-- example multiple checks SELECT column1 , CASE WHEN column2 IN ('A', 'B', 'V') THEN NULL ELSE column1 END FROM table_name -- alternative SELECT column1, NullIf(NullIf(NullIf(column2, 'A'), 'B'), 'V') FROM table_name
5. Convert Function
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
I used to use CAST instead CONVERT, however CONVERT is more powerful, especially when it comes to dates. SQL Server comes with a few dates formats obtain by providing a value for style parameter:
SELECT CONVERT(varchar(10), GetDate(),101) -- US format: mm/dd/yyyy SELECT CONVERT(varchar(10), GetDate(),102) -- ANSI format: yyyy.mm.dd SELECT CONVERT(varchar(10), GetDate(),103) -- British/French dd/mm/yyyy SELECT CONVERT(varchar(10), GetDate(),104) -- German format: dd.mm.yyyy SELECT CONVERT(varchar(12), GetDate(),105) -- Italian format: dd-mm-yyyy SELECT CONVERT(varchar(12), GetDate(),106) -- dd mon yyyy SELECT Upper(CONVERT(varchar(12), GetDate(),106)) -- upper case of: dd mon yyyy SELECT CONVERT(varchar(8), GetDate(),112) -- ISO format (preferred): yyyymmdd SELECT CONVERT(varchar(10), GetDate(), 23) -- yyyy-mm-dd SELECT CONVERT(varchar(23), GetDate(),126) -- yyyy-mm-ddThh:mi:ss.mmm
The conversion of a date to a string is mainly required by the need to have the date in a special format or not allow Excel to format the date to its default.
6. IsNumeric Function
IsNumeric ( expression )
This function shouldn't miss when casting string values to numeric values and doing operations with them.
SELECT CAST(column1 as decimal(12,2)) + 101 FROM table_name WHERE IsNumeric(column1)=1
Please note that when multiple constraints are used, the IsNumeric function should appear first after the WHERE clause, a query like the next one might raise an error if one of the column1 values is not numeric.
-- example SELECT CAST(column1 as decimal(12,2)) + 101 FROM table_name WHERE column2 = 'value' AND IsNumeric(column1)=1 -- alternative writing (recommended) SELECT CAST(column1 as decimal(12,2)) + 101 FROM table_name WHERE IsNumeric(column1)=1 AND column2 = 'value'
7. IsDate Function
IsDate( expression )
It works the same way as IsNumeric, but on date data types. Thus if the provided values is a date, it will return 1, otherwise 0.
SELECT IsDate(GetDate()) SELECT IsDate('11/31/2007') --november has only 30 days SELECT CASE IsDate('11/31/2007') WHEN 1 THEN 'Yes' ELSE 'No' END
Note:
For more information on the function is always a good idea to consult Microsoft Technical documentation.
For more information on the function is always a good idea to consult Microsoft Technical documentation.
No comments:
Post a Comment