SQL CR LF's - how to duplicate

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
sticarAsked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
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
 
jrb1Commented:
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
 
sticarAuthor Commented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
appariCommented:

or try CTRL + Enter
0
 
arbertCommented:
What front-end are you using and what datatype are you storing the data in?
0
 
arbertCommented:
you can also use REPLACE to make sure all the CRLFs are gone before you store the data.
0
 
sticarAuthor Commented:
Darn, Ctrl+Enter didn't duplicate it.

I'm using .NET as the front end.  Singleline, wrap:true
SQL Data type: varchar(30)
0
 
appariCommented:
may be the old DOS way, ALT + 10 or 13 ◙♪( from numric key pad)
0
 
appariCommented:

but DOS WAY not working on EE:)
0
 
sticarAuthor Commented:
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
 
sticarAuthor Commented:
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
 
appariCommented:
then may be it is converting some no to equivalent unicode character?
carraige return is 
just try
0
 
appariCommented:

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
 
sticarAuthor Commented:
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
 
sticarAuthor Commented:
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
 
jrb1Commented:
Yes, you could use replace to clean up the data and to prevent any more from being entered.
0
 
sticarAuthor Commented:
Do you have a code Example for the most effient way?
0
 
jrb1Connect With a Mentor Commented:
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
 
sticarAuthor Commented:
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
 
sticarAuthor Commented:
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
 
jrb1Commented:
It should be a safe way to ensure the data is clean.  Not a lot of down side.
0
 
sticarAuthor Commented:
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
 
jrb1Commented:
Hmmmm...you could also clean the data on the web page, but I don't know that that would be any less work.
0
 
sticarAuthor Commented:
Ok.  I'm just going to do the replace on all the fields in the sql stored procedure.

Thanks ALL for your help!
0
All Courses

From novice to tech pro — start learning today.