?
Solved

SQL CR LF's - how to duplicate

Posted on 2005-03-03
24
Medium Priority
?
853 Views
Last Modified: 2010-05-18
My SQL DB has some hidden characters (13,10), but only in a couple of records.  The records come in from a website.  I'm puzzled on how they got there and all the others are fine.  I cannot create a record online and duplicate the CRLF's in the SQL DB.  I do have wordwrap on - even though it won't let you go to the next line.  On enter, the "next" button is pushed.

If I type in some of the fields and hit enter - it goes to the next page - but in my SQL DB, there still isn't a CRLF.  I need to be able to duplicate this to make sure that turning off wordwrap fixes it.

Any ideas?  I tried ^M and ^J for the CRLF and it still doesn't insert it into the DB from the website.

THANKS
0
Comment
Question by:sticar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 5
  • +1
24 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 13454481
If you have a multiple line text file and copy from that and paste to the website, will it take the CR/LF then?  You may not be able to enter it directly on the website, but with paste it may go.
0
 

Author Comment

by:sticar
ID: 13454821
That's a great idea, I didn't think of that.  I just tried it, but it still didn't get to SQL.  I'll try different browsers to see.
0
 
LVL 39

Expert Comment

by:appari
ID: 13456056

or try CTRL + Enter
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

Expert Comment

by:arbert
ID: 13456205
What front-end are you using and what datatype are you storing the data in?
0
 
LVL 34

Expert Comment

by:arbert
ID: 13456213
you can also use REPLACE to make sure all the CRLFs are gone before you store the data.
0
 

Author Comment

by:sticar
ID: 13458768
Darn, Ctrl+Enter didn't duplicate it.

I'm using .NET as the front end.  Singleline, wrap:true
SQL Data type: varchar(30)
0
 
LVL 39

Expert Comment

by:appari
ID: 13459034
may be the old DOS way, ALT + 10 or 13 ◙♪( from numric key pad)
0
 
LVL 39

Expert Comment

by:appari
ID: 13459045

but DOS WAY not working on EE:)
0
 

Author Comment

by:sticar
ID: 13459172
I tried the ALT+10 and 13 (it put weird characters in the form), but came out as ?? in SQL.  I also did Alt+ #9689 and Alt+9834 and it put JJ in sql.

0
 

Author Comment

by:sticar
ID: 13459205
A little more info:
The field is an address field and there is an apt field right after it.

What was in the address field was:
8 User Street
APT. 234

And nothing in the apt field.  Made me think that it was an autofill issue.  But I put an address in 2 different auto fills and on address2, I put an appartment number, but it still never came up in SQL.
0
 
LVL 39

Expert Comment

by:appari
ID: 13459267
then may be it is converting some no to equivalent unicode character?
carraige return is 
just try
0
 
LVL 39

Expert Comment

by:appari
ID: 13459336

how are you saving the data to DB?
may be SQL Injection by some one? in the entry filed entering something like 8 User Street' + char(13) + 'APT. 234
0
 

Author Comment

by:sticar
ID: 13459425
You have many great ideas that I would have never thought about!  I tried &#13 and it just copied it to the DB.  I also tried the SQL injection - which it cuts it off after 30 characters or so, but I still left the char(13) in there, and it just copied it as well to the SQL DB.

It sure seems like it's an Autofill program that is messed up somehow.  But, seems like we should be able to duplicate it.
0
 

Author Comment

by:sticar
ID: 13460569
I'm using a stored procedure to insert the data into the database.

Can I put the Replace function on every Column under Formula to get rid of them?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13462481
Yes, you could use replace to clean up the data and to prevent any more from being entered.
0
 

Author Comment

by:sticar
ID: 13472048
Do you have a code Example for the most effient way?
0
 
LVL 34

Accepted Solution

by:
arbert earned 1200 total points
ID: 13482706
You could either do it on your ASP page, or in your Stored proc:

replace(replace(yourfield, chr(13),""),chr(10),"")

the above would be for the asp side of things.


For sql, this would work (@yourfield would be the input variables in the proc):

replace(replace(@yourfield, char(13),''),char(10),'')
0
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 800 total points
ID: 13482970
To clean up the data:

update table
set col1 = replace(col1, char(13)+char(10),'');

Same thing could be done in your program.  Replace the CRLF with a null string anywhere in the string.
0
 

Author Comment

by:sticar
ID: 13486821
Great.  I'll try the replace.  I just talked to the guy that entered the info, and he has a Mac and uses Safari for his browser.  Must be something weird with Mac and .net.  Maybe I can duplicate it somehow if I can get to a Mac.
0
 

Author Comment

by:sticar
ID: 13487821
Well I duplicated it in Mac with cut and paste.  I then set the textbox to wordwrap=false, but for some reason in the code behind it doesn't show "wordwrap" on IE or Mac.  So, it didn't work.  I may have to use the "replace" function.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13487855
It should be a safe way to ensure the data is clean.  Not a lot of down side.
0
 

Author Comment

by:sticar
ID: 13487890
True.  I just wanted to be able to fix it without all the replaces I will have to do.  There are ALOT of fields.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13487917
Hmmmm...you could also clean the data on the web page, but I don't know that that would be any less work.
0
 

Author Comment

by:sticar
ID: 13487971
Ok.  I'm just going to do the replace on all the fields in the sql stored procedure.

Thanks ALL for your help!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question