database developer – Page 10 – Ramblings of a Crafty DBA

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…

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…