Can''t insert certain charcters into SQL table

Question

Post reply

Can''t insert certain charcters into SQL table

PKDC

SSChasing Mays

Points: 618
More actions
July 5, 2006 at 4:32 pm

#113528

I ran below script in Query ananlyzer
insert into lookup_tbl values('L,l,L,l')
However When I ran
select * from Lookup_tbl
I get the following result.
L,l,L,l
In addition, I was able to add following 'Lall' into the DB using in-house application.
However, when I search for the string in the DB, even though data exists, I was not able to find the string in the DB. In fact, SQl returned me no results.
select name_preferred from person_tbl where name_preferred = 'Lall'
No result was returned. I am really puzzled. Can anyone help?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

I'm puzzled as well.

What's wrong with your resultset from Lookup_tbl?

What did you expect?

_____________
Code for TallyGenerator

stax68 SSChampion Points: 11711 More actions · Answer 2

The answer is the difference between the single-quote character:

'

as a part of a string value, as for example ,

lastname
--------
O'Toole

(which is part of the data)
and as part of a string literal, for example:

set @string = 'Burton'.

(Where the two single-quote characters occur as part of the code, which tells the SQL parser that what is between them is a string value.)
If you try to execute

set @string = 'O'Toole'

then the SQL parser thinks that you are saying

set @string = 'O'

and then doesn't know what to do with the rest of your code:

Toole'

So in order to specify the data value

'

inside a string literal, you have to replace it with two single-quote characters:

set @string = 'O''Toole'

When you select @string, you will get:

--------
O'Toole

Because you were typing into the front end, the application treated your single-quotes as part of the data. To search for the string you entered, you would have to use:

select

name_preferred from person_tbl where name_preferred = '''Lall'''

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Sergiy SSC Guru Points: 110208 More actions · Answer 3

If that's a case I would suggest to get familiar with the syntax of the programming language before trying to use it.

BTW, it's not about only TSQL, it's about any language.

_____________
Code for TallyGenerator

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

And what better way of doing so than calling on the help of members of this community?

steve block SSCertifiable Points: 7698 More actions · Answer 5

The apostrophe trips up many a DBA. See discussion http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=290168