string function – Page 2 – Ramblings of a Crafty DBA

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…

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…