Microsoft SQL Server Query Features: Difference between revisions
(→trim) |
(→iif) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 9: | Line 9: | ||
<code>select trim(address1) from Customer</code> | <code>select trim(address1) from Customer</code> | ||
=== | ===Concat=== | ||
Use concat instead of the "||" characters used in Interbase to concatenate. | |||
Example... | |||
<code>CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,</code> | |||
===replace=== | ===replace=== | ||
| Line 21: | Line 24: | ||
===case=== | ===case=== | ||
===iif=== | ===iif=== | ||
Similar to using a case statement, iif gives allows you 2 responses to an if condition.. | |||
<code>iif(rtrim(LTRIM(INACTIVE_CODE))='00','Y','N') active_flag,</code> | |||
===windowing functions=== | ===windowing functions=== | ||
Latest revision as of 16:25, 27 February 2024
trim
Commands: trim, ltrim, rtrim The trim commands will trim spaces from the beginning and end of strings.
In older MS SQL you must use ltrim and rtrim together to trim both ends of a string...
select rtrim(ltrim(address1)) from Customer
Modern MS SQL allows for just trim...
select trim(address1) from Customer
Concat
Use concat instead of the "||" characters used in Interbase to concatenate.
Example...
CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,
replace
Replace singele quote with 2 single quotes:
replace(rtrim(ltrim(NAME)),',') "DRIVER_NAME",
left, right
case
iif
Similar to using a case statement, iif gives allows you 2 responses to an if condition..
iif(rtrim(LTRIM(INACTIVE_CODE))='00','Y','N') active_flag,
windowing functions
rank
derived queries
Derived queries, fequently seen as a "with" statement... allow for querying queries.