Ramblings of a Crafty DBA – Page 18 – Various Data Mining Techniques

String Functions – RTRIM()

April 20, 2013October 8, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the RTRIM() function today. The RTRIM() function takes a source string as input and returns a output string with all trailing spaces removed. The following example removes trailing spaces from a favorite movie title of mine.

-- Remove trailing spaces
select rtrim('WIZARD OF OZ    ') as str_phrase

1 2	-- Remove trailing spaces select rtrim('WIZARD OF OZ ') as str_phrase

output: 

str_phrase
------------
WIZARD OF OZ

output:

str_phrase

------------

WIZARD OF OZ

One thing to always worry about when using TSQL functions is how will it react with empty strings or NULL values? Both empty strings and NULL values as input return themselves…

String Functions – RIGHT()

April 19, 2013October 8, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the RIGHT() function today. The RIGHT() function takes the source string [S] and number of characters [X] as input and returns the [X] rightmost characters from string [S] as output. The following example grabs the last word from a favorite movie title of mine.

-- Return last word
select right('wizard of oz', 2) as str_word

1 2	-- Return last word select right('wizard of oz', 2) as str_word

output: 

str_word
-----------
oz

output:

str_word

-----------

One thing to always worry about when using TSQL functions is how will it react with empty strings, NULL values, or invalid values?…

String Functions – REVERSE()

April 18, 2013October 8, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the REVERSE() function today. The REVERSE() function takes a source string as input and returns each character in the reverse order as output. Therefore, the last character input string is now the first character in the output string. And the first character in the input string is last character in the output string. Let’s have some fun by using palidromes in the next example. The example below has two…

String Functions – REPLICATE()

April 17, 2013October 8, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the REPLICATE() function today. The REPLICATE() function takes a pattern string [S] and a integer replication count [C] as input. The function returns a string composed of pattern string [S] concatenated [C] times to itself as output. The example below replicates a pattern ‘XO’ five times.

-- A string of XO's
select replicate('XO', 5) as str_love;

1 2	-- A string of XO's select replicate('XO', 5) as str_love;

output: 

str_love
---------
XOXOXOXOXO

output:

str_love

---------

XOXOXOXOXO

One thing to always worry about when using TSQL functions is how will it react with a EMPTY strings or NULL…

String Functions – REPLACE()

April 16, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the REPLACE() function today. The REPLACE() function takes a source string, a search string and a replacement string as input. For each occurrence of the search string found in the source string, change the value to the replacement string. Again, I will be using my favorite movie title in this example. Who does not like the “Wizard of OZ?”. The example below replaces the word “of” with the word…

String Functions – QUOTENAME()

April 15, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the QUOTENAME() function today. The QUOTENAME() function takes a input of type sysname, nvarchar(128) and returns a string, nvarchar(258) with quote characters at front and end of the string. A optional string can be specified to redefine the default quote character for TSQL. The following example creates a table name in the adventure works database from two variables.

-- Set local variables
declare @var_schema sysname = 'SalesLT';
declare @var_name sysname = 'Product';

-- Make table name from ADW
select quotename(@var_schema) + '.' + quotename(@var_name) as my_name;

-- Set local variables

declare @var_schema sysname = 'SalesLT';

declare @var_name sysname = 'Product';

-- Make table name from ADW

select quotename(@var_schema) + '.' + quotename(@var_name) as my_name;

output:

my_name
-------------------
[SalesLT].[Product]

output:

my_name

-------------------

[SalesLT].[Product]

The default quote characters [] can be changed by…

String Functions – PATINDEX()

April 14, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the PATINDEX() function today. The PATINDEX() function takes a search pattern to find and a expression to search as input parameters. It returns the first position in which the search pattern was found. Unlike the CHARINDEX() function, wild card characters such as %, _, and ^ can be used used to define the pattern. In a nutshell, it is a fuzzy match that is performed. The following example is…

String Functions – NCHAR()

April 13, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the NCHAR() function today. The NCHAR() function takes an integer value, UNICODE value from 0 to 65535 and returns a single UNICODE character string. The following example returns the character ‘0’ given the UNICODE value of 48.

-- ASCII/UNICODE value for zero
declare @var_tmp int = 48;

-- Return the single character, zero
select nchar(@var_tmp) as single_nchar;

-- ASCII/UNICODE value for zero

declare @var_tmp int = 48;

-- Return the single character, zero

select nchar(@var_tmp) as single_nchar;

output:

single_nchar
-----------
0

output:

single_nchar

-----------

One thing to always worry about when using TSQL functions is how will it react with a UNKNOWN or NULL values?

-- UNKNOWN value returns NULL
select nchar(NULL) as single_nchar;

1 2	-- UNKNOWN value returns NULL select nchar(NULL) as single_nchar;

output:

single_nchar
-----------
NULL

output:

single_nchar

-----------

NULL

Another thing to test is how the…

String Functions – LTRIM()

April 12, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the LTRIM() function today. The LTRIM() function takes a source string as input and returns a output string with all leading spaces removed. The following example removes leading spaces from a favorite movie title of mine.

-- Remove leading spaces
select ltrim('    WIZARD OF OZ') as str_phrase

1 2	-- Remove leading spaces select ltrim(' WIZARD OF OZ') as str_phrase

output: 

str_phrase
------------
WIZARD OF OZ

output:

str_phrase

------------

WIZARD OF OZ

One thing to always worry about when using TSQL functions is how will it react with empty strings or NULL values? Both empty strings and NULL values as input return themselves…

String Functions – LOWER()

April 11, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the LOWER() function today. The LOWER() function takes a source string as input and returns a output string with all alpha characters converted to lower case. The following example converts the favorite movie title of mine to lower case.

-- Make my string lower case
select lower('WIZARD OF OZ') as str_phrase

1 2	-- Make my string lower case select lower('WIZARD OF OZ') as str_phrase

output: 

str_phrase
------------
wizard of oz
</span>

output:

str_phrase

------------

wizard of oz

</span>

One thing to always worry about when using TSQL functions is how will it react with empty strings or NULL values? Both empty strings and NULL values as…