I need a SQL Expert
Holly | Posted 12:19am 27. August 2001 Server Time |
This is really a SQL question, not so much web development, but maybe there's a SQL expert out there. I have a table with a long text field (VARCHAR2(500)) which often contains one or more carriage control/line feed characters. Is there a way to read this field and replace the cc/lf with a space? I could use the REPLACE function, but how do I represent the cc/lf in the REPLACE?
SELECT REPLACE(LONG_TEXT_FIELD, '???',' ') FROM TABLE_NAME
Anybody know how to do it? |
Bcooper | Posted 1:19pm 27. August 2001 Server Time |
I don't think you can use it in a SQL statement, but if you can process the field with VB code, I think the constant vbnewline is equivalent to cc/lf.
Bob Cooper, MCP
IT Support Technician
tribe | Posted 1:54pm 27. August 2001 Server Time |
Here is an article that should help:
http://support.microsoft.com/support/kb/articles/Q164/2/91.asp?LN=EN-US&SD=gn&FR=0&qry=line%20feed&rnk=9&src=DHCS_MSPSS_gn_SRCH&SPR=SQL
r@ndy | Posted 12:16am 28. August 2001 Server Time |
I think you have to read the field first and then use
fieldnew=Replace(fieldold, vbCrlf, " ")
Holly | Posted 12:21am 28. August 2001 Server Time |
Wow! I found out how to do this! It took lots of searching, but here's the answer, in case any of you run across this problem.
You use the TRANSLATE function in your SQL. A carriage-control/line-feed entered by the user is stored as two characters on the database (carriage control plus line feed), so you have to do the TRANSLATE twice, but it works.
Select translate(translate(long_text_field,chr(10),' '),chr(13),' ') alias_name from table_name
The field alias_name is returned as a text field with line feeds replaced by two spaces.
This comes in very handy when you are trying to create a text file and open it in an Excel spreadsheet. When there are line feed characters in your text, Excel interprets them as a new line on the spreadsheet, which makes for a very nasty-looking spreadsheet.
Reply to Post I need a SQL Expert
|
|
|
