string function – Page 3 – Ramblings of a Crafty DBA

String Functions – DIFFERENCE()

April 7, 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 DIFFERENCE() function today. The DIFFERENCE() function takes two string arguments and computes how different the two strings sound when spoken. A return value of 4 means the strings sound the same while a value of 0 means the strings sound totally different. When using the difference function, you should look at the SOUNDEX() function. This function calculates a four-character code that is based on how the string sounds…

String Function – CONCAT()

April 6, 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 CONCAT() function today. The CONCAT() function takes two or more string arguments and combines them into one string. Any arguements that are not strings are converted to a string. All NULL values are converted to the empty string ‘ ‘. The following example takes seven input arguements of various data types and returns one string output.

-- Local variable
declare @var_tmp varchar(32);

-- Combine strings into one
select @var_tmp = concat('Fi', 've', ' plus', NULL, ' four equals nine (', 9, ').');

-- Show the resulting string
select @var_tmp as one_string;

-- Local variable

declare @var_tmp varchar(32);

-- Combine strings into one

select @var_tmp = concat('Fi', 've', ' plus', NULL, ' four equals nine (', 9, ').');

-- Show the resulting string

select @var_tmp as one_string;

output: 

one_string
--------------------------------
Five plus four equals nine (9).

output:

one_string

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

Five plus four equals nine (9).

One thing to always worry about when using TSQL functions…

String Function – CHARINDEX()

April 5, 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 CHARINDEX() function today. The CHARINDEX() function takes a expression to find, a expression to search and a optional start position. It returns the first position in which the search string was found. The following example is based upon the television jingle that I heard as a kid for Trix cereal. The example below contains four test cases: Word is found in target string. Word is not found in…

String Function – CHAR()

April 4, 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 CHAR() function today. The CHAR() function takes an integer value, ASCII code, from 0 to 255 and returns a single character string. The following example returns the character ‘0’ given the ASCII code of 48.

-- ASCII code for zero
declare @var_tmp int = 48;

-- Return the single character
select char(@var_tmp) as single_char;

-- ASCII code for zero

declare @var_tmp int = 48;

-- Return the single character

select char(@var_tmp) as single_char;

output:

single_char
-----------
0

output:

single_char

-----------

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 char(NULL) as single_char;

1 2	-- UNKNOWN value returns NULL select char(NULL) as single_char;

output:

single_char
-----------
NULL

output:

single_char

-----------

NULL

Another thing to test is how the function…

String Function – ASCII()

April 3, 2013October 11, 2017 admin

I am going to write a series of very short articles or tidbits on Transaction SQL (TSQL) functions. I will start exploring the string functions today in alphabetical order. Before the invention of UNICODE, we were able to express only 128 different characters with 7 bit ASCII code. Transaction SQL (TSQL) still supports the ASCII() function that takes a CHAR or VARCHAR datatype as input and returns a INT datatype as output. The following example returns the ASCII code for a space which is a value of 32.

-- A simple space
declare @var_tmp char = ' ';

-- Return ascii #
select ascii(@var_tmp) as ascii_code;

-- A simple space

declare @var_tmp char = ' ';

-- Return ascii #

select ascii(@var_tmp) as ascii_code;

output:

ascii_code
-----------
32

output:

ascii_code

-----------

…