Archive

Posts Tagged ‘string function’

SQL Server – "Denali” – String Concatenations using CONCAT()

July 25, 2011 2 comments

“Denali” introduces a new function CONCAT() for string concatenation. CONCAT() returns a string by concatenating two or more strings. CONCAT() requires 2 to 254 arguments.

for example, CONCAT() can be used as below:

SELECT CONCAT('SQL ', 'Server') AS Result

Result Set:

Result

———-

SQL Server

 

(1 row(s) affected)

The same can be achieved by using '+' operator:

SELECT 'SQL ' + 'Server' AS Result

Result Set:

Result

———-

SQL Server

 

(1 row(s) affected)

Then, why to use CONCAT() ?, There are two main reasons to use CONCAT() instead of '+':

1. All arguments to CONCAT() are converted implicitly to string type, when using '+' you
    need to user CAST() or CONVERT() to string type.
2. NULLs are converted to empty string, when using '+', the result will be NULL, (anything +
    NULL = NULL)

Below example demonstrates the use of CONCAT() with NULL values and different data types.

CREATE TABLE tempTable

(

       Region        VARCHAR(30),

       Name          VARCHAR(30),

       SalesYTD      NUMERIC(14,2)

)

 

 

INSERT tempTable VALUES

('North America', 'Northwest', 123237.00 ),

('South America', 'Northwest',  37534.00 ),

('South America', NULL,         39667.00 ),

('North America', 'Southwest', 164232.00 )

 

SELECT CONCAT(Region, ' ', Name, ' ', SalesYTD) AS Result

FROM   tempTable

Result Set:

Result

——————————————————————————————————-

North America Northwest 123237.00

South America Northwest 37534.00

South America  39667.00

North America Southwest 164232.00

 

(4 row(s) affected)

If you try using '+' operator to concatenate, you will receive a data type conversion error:

SELECT Region + ' ' + Name + ' ' + SalesYTD AS Result

FROM   tempTable

Result Set:

Result

—————————————

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

And, NULL value in any of the inputs will result in NULL:

SELECT Region + ' ' + Name AS Result

FROM   tempTable

Result Set:

Result

————————————————————-

North America Northwest

South America Northwest

NULL

North America Southwest

 

(4 row(s) affected)

To get proper result using '+' operator, you can use COALESCE() to replace NULLs with empty string and CAST() to convert input data type to string:

SELECT Region + ' ' + COALESCE(Name,'') + ' ' +

       CAST(SalesYTD AS VARCHAR(20)) AS Result

FROM   tempTable

Result Set:

Result

———————————————————————————-

North America Northwest 123237.00

South America Northwest 37534.00

South America  39667.00

North America Southwest 164232.00

 

(4 row(s) affected)

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Categories: SQLServer, SQLServer 2012

SQL Functions – STUFF() and REPLACE()

July 6, 2011 4 comments

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @string1 VARCHAR(20) = 'Microsoft Server'

DECLARE @string2 VARCHAR(20) = 'SQL Server 2005'

 

SELECT      @string1 + ' -> ' + STUFF(@string1, 11, 0, 'SQL ')

            AS 'String 1',

            @string2 + ' -> ' + STUFF(@string2, 15, 1, '8 R2')

            AS 'String 2'

Result Set:

String 1                                 String 2

—————————————- ————————————-

Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2

 

(1 row(s) affected)

In the first string it inserts 'SQL ' at specified position – 11, the third argument 0 indicated the number of characters to be deleted before inserting the new string.

For second string, we have deleted one (1) character starting from position 15, which deletes '5', and then it inserts the new string at position 15 – '8 R2'.

 

REPLACE():

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = 'sql 2005, sql 2008, sql 2008 r2'

 

SELECT @string3, REPLACE(@string3,'sql','SQL')

Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2

 

(1 row(s) affected)

However, it is not limited to same number of characters:

DECLARE @string3 VARCHAR(35) = '2008 R2'

 

SELECT @string3, REPLACE(@string3,'20','SQL Server 2')

Result Set:

————–       ————————

2008 R2              SQL Server 208 R2

 

(1 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Functions – PATINDEX()

July 5, 2011 2 comments

CHARINDEX() can only be used to search a literal string in the specified expression. In other words you cannot use wildcards. PATINDEX() provides this capability. It takes two arguments, the pattern to be searched and the expression.

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @string VARCHAR(128)

SET @string =     'PATINDEX searches the string for the ' +

'first occurrence of a specified ' +

'character/String/pattern'

SELECT      PATINDEX('%s_t%', @string) AS 's t',

            PATINDEX('%pat%', @string) AS 'PATINDEX',

            PATINDEX('%pat[^i]%', @string) AS 'pattern',

            PATINDEX('%f[a-i]r%', @string) AS 'first'

Result Set:

s t         PATINDEX    pattern     first

———– ———– ———– ———–

17          1           87          38

 

(1 row(s) affected)

First column uses the _ Wildcard, which matches any one character, which is between 's' and 't'.

The second column uses % Wildcard, which searches for 0 or more characters. It is same as using: CHARINDEX('pat', @string).

Third column uses a ^ Wildcard, which matches the characters not matching the specified set. Hence, ignoring the 'PATINDEX'.

Last column matches the characters specified in the range using [ ] Wildcard. Since the set specifies that only 'a' to 'i', it ignores the 'for'.

Pattern matching is based on the data collation. We can use COLLATE to enforce a case-sensitive search:

SELECT      PATINDEX('%[S]tring%', @string) AS 'string',

            PATINDEX('%[S]tring%', @string COLLATE Latin1_General_CS_AI)

            AS 'String'

Result Set:

string      String

———– ———–

23          80

 

(1 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Functions – CHARINDEX()

July 4, 2011 5 comments

CHARINDEX() returns the starting position of an expression in another expression. It takes below arguments:

CHARINDEX ( exp1, exp2, position )

Where, exp1 = expression to be searched,

exp2 = the main expression which contains exp1,

position = this is optional, it specifies a position in exp2 from which the search for exp1 starts.

CHARINDEX() returns and INT/BIGINT based on the data type of exp2, which specifies the starting point of exp1. For data types declared with (MAX) it returns BIGINT.

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @string VARCHAR(128)

SET @string =     'CHARINDEX searches for the string for the ' +

                  'first occurrence of a specified character/string'

SELECT      CHARINDEX('Char', @string)    AS 'Position',

            CHARINDEX('Char', @string, 2) AS 'Position'

Result Set:

Position    Position

———– ———–

1           75

 

(1 row(s) affected)

In the above example, the first columns returns 1 as the string started with 'CHAR…', while in the second column the search was started from 'HARINDEX…', which resulted in returning the second occurrence of 'CHAR'.

Searches are based on the collation of the data. CHARINDEX() has performed a case-insensitive search as my current collation is set to Latin1_General_CI_AI. To perform a case-sensitive search here we can apply Latin1_General_CS_AI collation to @string:

DECLARE @string VARCHAR(128)

SET @string =     'CHARINDEX searches for the string for the ' +

                  'first occurrence of a specified character/string'

SELECT      CHARINDEX('char', @string COLLATE Latin1_General_CS_AI)

            AS 'Position'

Result Set:

Position

———–

75

 

(1 row(s) affected)

In this example, the first occurrence of 'Char' is ignored as it does not match the case.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe