pipe & SQL

I just found a nice little thing today (our DBA's didnt know about it either), you cant insert a pipe ( | ) directly with sql


insert into tblMyTable ( myField ) values ( "|" )

so, easy enough to change it to chr(124) and everything works well.

My Q is,  How am i suposed to know that??  Is there some doc's / people know of what other things i cann't just go striaght out and add (of course " and ', but any other whacky ones )

Open for any input (on topic)! -

points for all!!!

LVL 34
Who is Participating?
shanesuebsahakarnConnect With a Mentor Commented:
Better explanation than I can provide:

Only the | character has this problem.
seems u have used double qoutes to delimit a string.
this is not allowed in ms-sql...double qoutes are used to name variable/columns which include space.
to enclose a string, u need to use single qoute  

i tried following command and it worked
insert into tblMyTable ( myField ) values ( '|' )

chr(124) refers to the ascii value of the character and that you can use anytime to refer any other character, not limited to special character

if u need to insert a single qoute itself, u need to insert two of them after each other...
e.g. '''' = a single qoute
flavoAuthor Commented:
opps.. Yeah i know that.

the SQL bit refers to MS Access general garden variety of SQL.. sorry for the mis-understanding.

What im really after is what else to i have to convert to Chr(int)???

I can do " and '

You could cay i kinda know how to use access, and  i am well aware of such issues... (see profile)
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

flavoAuthor Commented:
>> insert into tblMyTable ( myField ) values ( '|' )

Didn't seem to in Access 97
flavoAuthor Commented:
good enough for me!
flavoAuthor Commented:
you can goto sleep now shane
Not yet, I'm still working :(
flavoAuthor Commented:
It must be about 2:30am..
3:30am, to be precise, but I have a data conversion that must be done by 9AM tomorrow morning and the data only arrived at 9PM.....
flavoAuthor Commented:
crazy!  I did 13hrs yesterday, that's enough (way too much considering i work for the gevernment.. that's 3weeks worth of work in 1 day)...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.