April 6, 2011 at 4:37 pm
I am doing a project for a client that parses the sms log of an iPhone. I have developed a program that generates SQL insert statements based of the log and have a question about escape charachters. The field 'Message' which you will see below containts the contents of a sms text message and the messages commonly contain the characters ',?," etc. that are illegal if I use them in my insert statement. Given the SQL statements below how can I allow for ',?," etc.???????
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'That wasnt fair - putting the $10 back into my bag when I wasnt looking...','Saturday 01 January 2011 ','15:26:02')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Hahha yea I wanted to slip it in your pocket but you had none ','Saturday 01 January 2011 ','15:55:41')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Finish the salad','Saturday 01 January 2011 ','15:55:52')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'My sister and I devoured it as soon as we got home lol','Saturday 01 January 2011 ','15:59:25')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I didnt realize how hungry I was until we started eating','Saturday 01 January 2011 ','16:02:25')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'You kept asking for a steak ','Saturday 01 January 2011 ','16:02:42')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'I have steaks','Saturday 01 January 2011 ','16:02:58')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'I have steaksLike 4 times a week','Saturday 01 January 2011 ','16:02:58')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'You said salad I want real food hahaha','Saturday 01 January 2011 ','16:03:24')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I love steak... Well I just love food in general','Saturday 01 January 2011 ','16:04:03')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'You going out again tonight ','Saturday 01 January 2011 ','16:05:56')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Lol I like salad but its leaves I wanted realllll food. Yeah actually Matt invited Larisa out to some party tonight so she wants Kate and I to go with her','Saturday 01 January 2011 ','16:07:29')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Ya Ill be there as well he wants to hop around the city hes usually pretty','Saturday 01 January 2011 ','16:09:30')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Ya Ill be there as well he wants to hop around the city hes usually prettyGood at planning shit so will see ','Saturday 01 January 2011 ','16:09:30')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Kate said shell go if her feet arent too bruised up haha','Saturday 01 January 2011 ','16:10:24')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Larisa has got to be aching ','Saturday 01 January 2011 ','16:10:38')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'She fell like 3 times once on her face and then got back up and laughed and danced away ','Saturday 01 January 2011 ','16:10:57')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I know she told me haha she asked me if she was climbing over your poker tables bc she had green stuff all over her legs','Saturday 01 January 2011 ','16:12:14')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Hahha no she had that from the floor at first I thought they were black and blues but it was just ink ','Saturday 01 January 2011 ','16:12:49')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Ohh so thats what that was.. I have some blue stains on my legs as well but it cant be from the floor bc I didnt fall','Saturday 01 January 2011 ','16:16:45')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Hmmmmmm ','Saturday 01 January 2011 ','16:18:31')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'What about the table cloth on the bar that was blue ','Saturday 01 January 2011 ','16:19:20')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Tablecloths dont usually give off color tho','Saturday 01 January 2011 ','16:20:22')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'If the have alcohol on them they can cheap ones ','Saturday 01 January 2011 ','16:20:26')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'This might just have to remain a mystery...','Saturday 01 January 2011 ','16:20:37')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'I feel like a f***ing detective right now ','Saturday 01 January 2011 ','16:20:44')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Haha Ill give you $10 if you can figure out where the stain came from','Saturday 01 January 2011 ','16:22:28')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Lol I think its the drapes so Ill have to get proof also ','Saturday 01 January 2011 ','16:22:52')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Proof','Saturday 01 January 2011 ','16:23:42')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'I have to prove it or else how do I get my $10 dollars ','Saturday 01 January 2011 ','16:23:57')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Well yeah but how will you prove it','Saturday 01 January 2011 ','16:24:37')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Ill find a way ','Saturday 01 January 2011 ','16:26:57')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Okay I look forward to it bc Id really like to know how those stains got there as well','Saturday 01 January 2011 ','16:29:24')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Yum that looks delicious','Sunday 02 January 2011 ','05:49:22')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'You home!','Sunday 02 January 2011 ','05:55:21')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'No I just dropped Kate off','Sunday 02 January 2011 ','05:56:29')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Stay alive dont text and drive ','Sunday 02 January 2011 ','05:56:18')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Im a great multi-tasker as you can clearly see','Sunday 02 January 2011 ','05:57:25')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Mad skills eddy mad skills text me when you get home so I know you and the Larisa are safe ','Sunday 02 January 2011 ','05:58:25')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Are you home yet','Sunday 02 January 2011 ','05:59:18')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I mean at your office','Sunday 02 January 2011 ','05:59:22')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Nope going to diner ','Sunday 02 January 2011 ','05:59:15')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I think Ill make pasta when I get home...','Sunday 02 January 2011 ','06:00:23')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Pasta at 6:00 I love it ','Sunday 02 January 2011 ','06:00:47')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'home!!','Sunday 02 January 2011 ','06:06:10')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Pretty quick speed racer ','Sunday 02 January 2011 ','06:05:57')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I feel like I like 7 pounds the past two days I havent had time to eat','Sunday 02 January 2011 ','06:06:28')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Lost 7 lbs*','Sunday 02 January 2011 ','06:06:48')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Lol yea I understood ','Sunday 02 January 2011 ','06:06:57')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Lol you never use round numbers ','Sunday 02 January 2011 ','06:07:30')
April 8, 2011 at 8:47 am
dont think ? is an illegal character. I've never had issues inserting it...
declare @Foo as table (foo char(4))
insert into @Foo
select 'foo?'
select * from @Foo
returns the text 'foo?' on my machine.
piece of advice though, dont post people's text messages on web forums! 😀
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
April 8, 2011 at 10:05 am
try using the character codes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply