database developer – Page 11 – Ramblings of a Crafty DBA

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…

String Functions – LEN()

April 10, 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 LEN() function today. The LEN() function takes one string argument as input and returns one integer argument, the size of the input string, as output The following example determines the title length of a favorite movie of mine.

-- My favorite movie title returns a size of 12
select len('wizard of oz') as str_size

1 2	-- My favorite movie title returns a size of 12 select len('wizard of oz') as str_size

output: 

str_size
-----------
12

output:

str_size

-----------

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

-- Null returns Null
select len(NULL) as str_size
</span>

-- Null returns Null

select len(NULL) as str_size

</span>

output: 

str_size
-----------
NULL

output:

str_size

-----------

NULL

Last but not least, the…

String Functions – LEFT()

April 9, 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 LEFT() function today. The LEFT() function takes the source string [S] and number of characters [X] as input and returns the [X] leftmost characters from string [S] as output. The following example grabs the first word from a favorite movie title of mine.

-- Return first word
select left('wizard of oz', 6) as str_word

1 2	-- Return first word select left('wizard of oz', 6) as str_word

output: 

str_word
-----------
wizard

output:

str_word

-----------

wizard

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 – FORMAT()

April 8, 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 FORMAT() function today. The FORMAT() function takes a value [V], a format string [F], and a optional culture specification [C] as input parameters. It returns a locale-aware formatted string. For general conversions, consider using the CAST or CONVERT functions. I personally think this string function was a valuable addition to SQL Server 2012. Please refer to the .NET Framework 4.5 formatting types for both standard and custom format…

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…