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