Microsoft SQL Server Query Features: Difference between revisions
(Created page with "===trim=== ltrim rtrim select trim(address1) from Customer ===concat=== ===replace=== Replace singele quote with 2 single quotes: <code>replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",</code> ===left, right=== ===case=== ===iif=== ===windowing functions=== ===rank=== ===derived queries=== Derived queries, fequently seen as a "with" statement... allow for querying queries.") |
(→iif) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
===trim=== | ===trim=== | ||
ltrim rtrim | Commands: trim, ltrim, rtrim | ||
The trim commands will trim spaces from the beginning and end of strings. | |||
select | In older MS SQL you must use ltrim and rtrim together to trim both ends of a string... | ||
<code>select rtrim(ltrim(address1)) from Customer</code> | |||
Modern MS SQL allows for just trim... | |||
<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 16: | 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.