quotename not working for nvarchar(max) columns

Padmanabhan, Venkatesh 241 Reputation points
2021-03-26T16:01:40.343+00:00

Hi.

I have a column which is of type nvarchar(max). I am trying to get this data moved to a text file using BCP option.
The values in the column should be enclosed in double quotes, while generating the data out , additional whitespaces , LR should also be removed.

I have tried adding quotename(column_name,"") , but this gives me a NULL value.
Tried this, but not working : QUOTENAME(REPLACE(LTRIM(RTRIM(column_name)),CHAR(10), ''),"")

Example: The output should be like : "This is a test data "

How to fix this ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,486 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-03-31T02:35:55.503+00:00

    Hi @Padmanabhan, Venkatesh ,

    Please have another try with below:

    BCP "SELECT quotename(TRIM([CustomColumnName]),char(34)) FROM table"  queryout "filepath" -T -S server -d DB -b 500 -c -C 65001 -t~  
    

    Or:

    bcp table out "filepath" -c -T -t"\",\"" -r"\"\n\"" -S SERVER  
    

    If above is still not working,please provide CREATE TABLE statements for your table together with INSERT statements with sample data.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Dan Guzman 9,201 Reputation points
    2021-03-26T18:48:20.243+00:00

    From the QUOTENAME documentation:

    'character_string'
    Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

    0 comments No comments

  2. Erland Sommarskog 100.1K Reputation points MVP
    2021-03-26T22:29:15.777+00:00

    As Dan says, quotename is limited, as it intended for identifiers..

    See here for some user-defined functions you can use: http://www.sommarskog.se/dynamic_sql.html#quotename.


  3. MelissaMa-MSFT 24,176 Reputation points
    2021-03-29T08:37:49.073+00:00

    Hi @Padmanabhan, Venkatesh ,

    What is your original value of column_name while the output should be like : "This is a test data "?

    Please refer below examples:

    declare @t nvarchar(max)='This is a test data '  
      
    select QUOTENAME(REPLACE(TRIM(@t),CHAR(10), ''),'"')  
      
    select QUOTENAME(TRIM(@t), '"')   
      
    select ('"' + replace(TRIM(@t), '"', '""') + '"')  
    

    Output:
    "This is a test data"

    declare @t1 nvarchar(max)='This is "a" test data '  
      
    select QUOTENAME(REPLACE(TRIM(@t1),'"', ''),'"')  
      
    select ('"' + replace(TRIM(@t1), '"', '') + '"')  
    

    Output:
    "This is a test data"

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.