how to handle delimiter in SQL?

Question

how to handle delimiter in SQL?

jps

Newbie

Points: 1
More actions
March 29, 2004 at 1:36 pm

#161254

Hi there,
I made a text input box, and saved inputed text into DB.
the problem i encountered is users might input any char like ' which is delimiter of SQL.
I tried to use QUOTENAME to solve this problem, but some time it cut off the input.
eg.
input:
sss''"hh""~!@#$%^&*() \|{}+_~`''jkljkl
QUOTENAME works correctly.

but if input likes:
The primary social function of a 23-year-old female is to secure a boyfriend of the make-your-friends-jealous grade. Provisions for achieving such a rank include, but are not limited to: initiating clichéd romance measures (i.e.; flowers, sappy phone calls, cheesy nicknames),acceptable employment status (employed), and the capacity for intellectual debate partnered withthe understanding that I win, no, I win. This isn’t a debate. Although I’m fairly low maintenance *grin*, I have some ridiculously obscure standards that even I’m not fully aware of. But if you can fly planes, rescue kittens from burning buildings or buy me my own country, I might be impressed. Knowledge of internal motor vehicle components would be nifty. Heavy object repositioning skills would also be a bonus. A high aptitude for discernment of sardonic overtones is a must. A dictionary might come in handy. I like pencils, chocolate, political satire, cartoons, the click noise that typewriters make, cotton candy, driving fast and fluffy stuffed animals. I collect blue glass stuff, daggers, swords, and dust bunnies. I willingly watch TV. I’ve been known to frequent movie theaters, consume non-doctor-recommended-food items, attend sporting events, say really mean spirited words like “dorkfish,” date musicians, and strike fear into the hearts of high pressure salesmen everywhere for invading the JUST BROWSING bubble with a single look. I’m not a member of the following classifications: Jesus people, union workers, *insert word here’s* anonymous, the Alanis Morrisette fan club, the Grateful Dead, or girl scouts. I may be a hippy flower child. My lab results haven’t come back yet. I’ve been labeled “corporate” before (possibly due to aspirations of legal defenderness, possibly due to exorbitant amounts of time spent in profitable-shopping-entity bookstores who shall remain nameless, or perhaps my uncanny ability to buy into mass-mediated fads), so it’s iffy. Height/age: 5’7+

but it was cut off to :
The primary social function of a 23-year-old female is to secure a boyfriend of the make-your-friends-jealous grade. Provisions
if removing QUOTENAME, works correctly.

so who knows any solutions?
Thx

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

QUOTENAME return value is nvarchar(129) therfore will only return 129 characters. YOur best best is to use a storedprocedure and use a command object with parameters to pass your data to SQL that way you don't have to worry about 's in your text.

How are you currently passing the data to SQL

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736162

March 29, 2004 at 2:42 pm

#500948

agree with above.

dMacey SSC Enthusiast Points: 146 More actions · Answer 3

Obviously using a sproc is the best solution; however for some reason this cannot be done a workaround is to use a replace function to substitute the single quote for some unprintable or rarely used character before putting the string into the database. Just remember to do the substitution in reverse before displaying the data.

Daniel

Matthew Cunningham Old Hand Points: 325 More actions · Answer 4

One of the easiest ways to use strings which contain single quotes is to replace all instances of the single quote with two single quotes. SQL Server handles two single quotes inside of a literal string as a single quote. Try running this for example:

SELECT 'that''s mine'

Will return the following:

-----------

that's mine

(1 row(s) affected)

You can use the replace function in VB to easily convert the string.

netedk SSC Veteran Points: 227 More actions · Answer 5

I had a similar issue and I wrote a fucntion to achieve this.

CREATE FUNCTION quoteSurround(@STR VARCHAR(4000))

RETURNS VARCHAR(4000)

AS

BEGIN

RETURN('"' + @STR + '"')

END

Now you can call this as follows:

SELECT quoteSurround(fieldName)

FROM table1

WHERE id = 1234

hope this helps....

Steve SSCommitted Points: 1844 More actions · Answer 6

Since you say that you made the text input box, I assume also that you have access to the vb code. That being said, I have always used the following solution when i need to "correct" input from users for insertion into SQL. Before inserting the value of the text box use the replace function in vb to change all ' (ASCII 39) to ’ (ASCII 146). This way you avoid the delimiter problem while visually retaining the same text the user input.

The statement might read something like

BEFORE:text1.value="This is my user's text"

sMyVar = Replace(text1.value,chr(39),chr(146))

AFTER:sMyVar = "This is my user’s text"

Steve

Calvin Lawson SSChampion Points: 11030 More actions · Answer 7

Steve,

Very cool! Thumbs up for obscure but useful ASCII knowledge.

cl

Signature is NULL