question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked MelissaMa-msft answered

quotename not working for nvarchar(max) columns

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-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Did you try quotename(column_name, '"') too?

0 Votes 0 ·

Hi @PadmanabhanVenkatesh-6789,

Could you please validate all the answers so far and provide any update?


Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @PadmanabhanVenkatesh-6789,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
0 Votes"
DanGuzman answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Viorel-1 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried this, but the output is not coming correctly:

('""' + replace(column, '""', '""""') + '""')

The quotes are not between the text.

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog PadmanabhanVenkatesh-6789 ·

Not sure what you mean with "The quotes are not between the text."

Please post an example with an input string and the output string you want.

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 PadmanabhanVenkatesh-6789 ·

Did you try ('"' + replace(column, '"', '""') + '"')?

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ErlandSommarskog commented

Hi @PadmanabhanVenkatesh-6789,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi.
I tried the below code, but no records are getting passed through BCP. I can get the rows returned in sql, but bcp is not giving data.

BCP "SELECT ( '"' + REPLACE(LTRIM(RTRIM([CustomColumnName])),CHAR(32) +CHAR(32) + CHAR(10), '') + '"')
FROM table " queryout "filepath" -T -S server -d DB -b 500 -c -C 65001 -t~

What am I missing ?
Thanks

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog PadmanabhanVenkatesh-6789 ·

It seems that you also have the thread https://docs.microsoft.com/en-us/answers/questions/335100/bcp-error-copy-direction-must-be-in-out-or-format.html?childToView=337395#comment-337395

I guess they are about the same problem, so let's stick to the other thread.

0 Votes 0 ·