April 2013 – Ramblings of a Crafty DBA

String Functions – UPPER()

April 27, 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 UPPER() function today. The UPPER() function takes a source string as input and returns a output string with all alpha characters converted to upper case. The following example converts the favorite movie title of mine to upper case.

-- Make my string upper case
select upper('wizard of oz') as str_phrase

1 2	-- Make my string upper case select upper('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…

String Functions – UNICODE()

April 26, 2013October 8, 2017 admin

I am going to write a series of very short articles or tidbits on Transaction SQL (TSQL) functions. I will exploring the UNICODE() function today. Transaction SQL (TSQL) supports the UNICODE() function that takes a NCHAR or NVARCHAR datatype as input and returns a INT datatype as output. The output value corresponds to the unicode characters location in a chart. This value can range from 0 to 65535. The following example returns the unicode value for a space which is a value of 32.

-- A simple space
declare @var_tmp1 nchar = ' ';

-- Return ascii #
select unicode(@var_tmp1) as unicode_val;

-- A simple space

declare @var_tmp1 nchar = ' ';

-- Return ascii #

select unicode(@var_tmp1) as unicode_val;

output:

unicode_val
-----------
32

output:

unicode_val

-----------

One thing to always…

String Functions – SUBSTRING()

April 25, 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 SUBSTRING() function today. The SUBSTRING() function allows a developer to slice and dice a string into pieces. It takes a source string [T], a integer starting position [S], and a the number of characters [C] to return as input parameters. It returns a string of [C] characters from position [S] as output. The example below extracts one word from a famous movie title.

-- Extract one word
select substring('wizard of oz', 8, 2) as str_word

1 2	-- Extract one word select substring('wizard of oz', 8, 2) as str_word

output: 

str_word
--------
of

output:

str_word

--------

One thing to…

String Functions – STUFF()

April 24, 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 STUFF() function today. The STUFF() function takes a source string [S], a starting position [P], a total length [L] and a replacement string [R] as input parameters. It removes [L] characters from the source string [S] starting at position [P] and replaces those changes with string [R]. From the definition of this function, one could easily write a custom user defined function with the same functionality using the…

String Functions – STR()

April 23, 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 STR() function today. The STR() function takes a floating point number [F] to be converted to a string, a integer number [L] representing the total length, and a integer number [D] representing the number of decimal digits as input. It returns a formatted string representing the floating point number. The length [L] includes decimal point, sign, numbers and spaces. The decimal digits [D] represents the number of digits…

String Functions – SPACE()

April 22, 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 SPACE() function today. The SPACE() function takes a positive integer replication count [C] as input. The function returns a string composed of a single space concatenated [C] times to itself as output. The example below adds one, two, and three spaces inside the title of a favorite movie of mine.

-- Add 1 + 2 + 3 spaces
select space(1) + 'wizard' + space(2) + 'of' + space(3) + 'oz' as str_phrase

1 2	-- Add 1 + 2 + 3 spaces select space(1) + 'wizard' + space(2) + 'of' + space(3) + '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…

String Functions – SOUNDEX()

April 21, 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 SOUNDEX() function today. The SOUNDEX() function calculates a four-character code that is based on how the string sounds when spoken. Please see my prior blog entry for a sample use of this function in conjunction with the DIFFERENCE() function. Soundex was developed by Robert C. Russell and Margaret K. Odell and patented in 1918 / 1922. The United States government created a variation named the American Soundex which…

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…