March 9, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/tamethosestringspart8.asp
March 24, 2002 at 9:16 pm
Hi Steve
In this article title "QUOTES" you missed a lot, but mentioned a general stuff that can be found everywhere...
I was expecting to see a work around when you have "'" single quote in your string, as well as tricky examples of single and double quotes.
Then using char/varchar fields and text fields. Perhaps in Part 9, call it Advanced String Manipulation.
I appreciate your posting.....
Thanks....Omer.
Omer
March 25, 2002 at 5:29 am
Although yes not earth shattering it did contain usefull information for those who have come across the same type of problem and presented a function that can be quite handy.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 25, 2002 at 9:25 am
Thanks, I'm was trying to present some information on a topic that may have stumped some people. I plan on continuing with other items as I find them and I'll look at some trickier examples next time.
Steve Jones
October 3, 2002 at 3:31 pm
Nice article.
QUOTENAME() does handle the case of imbedded single-quote characters in strings (or any other delimiter character). Try the examples from the article with delimiter character(s) in the test strings -- nice!
A problem I've encountered with QUOTENAME() is that it appears to work for strings up to only 128 characters, even though the return type is documented to be nvarchar(258). E.g. if I supply a 150-character literal I get back 128 characters:
print QUOTENAME( '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' , '''' )
Result:
'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678'
Am I doing something wrong?
Thanks...
October 4, 2002 at 9:45 am
Do you get this in QA or in an app? In QA, there is a 255 limit, which might be reduced to 128 for N' strings.
Steve Jones
October 4, 2002 at 10:03 am
Both QA and the app. Thanks.
Here's something interesting... (in QA) QUOTENAME() truncates if it is passed a long regular literal and returns NULL if it is passed a long national-style literal. E.g.
PRINT CASE
WHEN QUOTENAME( '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' , '''' )
IS NULL
THEN 'It is NULL'
ELSE 'It is not NULL'
END
PRINT CASE
WHEN QUOTENAME( N'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' , N'''' )
IS NULL
THEN 'It is NULL'
ELSE 'It is not NULL'
END
Results:
It is not NULL
It is NULL
October 4, 2002 at 3:16 pm
Interesting. I get the same thing.
I'll try to dig into this.
Steve Jones
March 24, 2004 at 9:24 pm
Good One. I learned new stuff. thanx a lot
balaji
Balaji
March 25, 2004 at 1:11 am
Simple stuff, and it is Great!!
My Blog:
March 25, 2004 at 7:53 am
Nice feature that I could use in future.
March 25, 2004 at 7:55 am
And note that it it still gets truncated if you increase QA's results limit to 8192 (instead of 256).
John Scarborough
MCDBA, MCSA
March 25, 2004 at 3:52 pm
Thanks. Glad that after a year or so, this stuff still applies.
March 25, 2005 at 7:52 am
This is a handy little feature that for some reason I've missed before. Thanks Steve.
-Vic
January 4, 2008 at 3:02 am
From BOL:
' character_string '
Is a string of Unicode character data. character_string is sysname.
and
sysname is functionally equivalent to nvarchar(128).
There's your limitation of 128 characters.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy