Home > SQL Server, T-SQL > Enclose String with Delimiter using QUOTENAME() T-SQL

Enclose String with Delimiter using QUOTENAME() T-SQL


QUOTENAME() function returns a Unicode string  of the user argument with the delimiter character. The delimiter can be a user defined quote character or a default quote character.

The default Quote character is [].

The character string of the quotename() function is limited to 128 characters only. If the Inputs character string is greater than 128 characters than the return value of the function is NULL.

The quote(delimiter) character can be a quotation mark ( ), a left or right bracket ( [] ), a double quotation mark ( ), a curly braces ( {} ) or a Parentheses ( () ) or a angular bracket ( <> ). If we specified Invalid quote character then the output will  be NULL.

Syntax

QUOTENAME ( ‘character_string’ [ , ‘quote_character’ ] )

Experiment

select QUOTENAME(‘SQL Server Rider’)
select QUOTENAME(‘SQL Server Rider’, ”)

These both select statements returns string  with the default delimiter.

Output: [SQL Server Rider]

select QUOTENAME(‘SQL Server Rider’, ””)
select QUOTENAME(‘SQL Server Rider’, ‘”‘)
select QUOTENAME(‘SQL Server Rider’, ‘<‘)

The output of these sql statements returns string with user specified quote character.

Output

‘SQL Server Rider’
“SQL Server Rider”
<SQL Server Rider>

select QUOTENAME(‘SQL Server Rider’, ‘?’)
The output of this SQL Statement is NULL. Because, the function contains invalid quote character.

We can use this QUOTENAME() function in the application to build a valid SQL Server delimited text. So, we can use this output string to create a CSV files.

For Readers

If you are using this function in your application then please write your comment about, how you are using this function in your application?

Thanks for reading

Advertisements
  1. August 7, 2015 at 7:59 am

    Almost exactly what I was looking for. However I will have to embed this into an UDF to only quote if the string contains blanks. Have to create files for data exchange and it requires it this way:
    value=SQLServerRider is ok, but with blanks it must be value=”SQL Server Rider”.
    So I’ll have an UDF to check cases…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: