472,985 Members | 2,740 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,985 software developers and data experts.

removing chr(13) and chr(10) from SQL

Hi.

Is there a way to remove carriage returns and line feeds from a field in SQL.

Here is what I have at the moment:

CAST(m.Description AS VARCHAR(8000))AS "Product Description"

(have had to cast as VarChar because of problems with aggregate functions in rest of query).

Problem is that this m.description field has carriage returns and line breaks in it .. how do I remove these inside the SQL statement?

thanks
Dec 21 '06 #1
3 53174
almaz
168 Expert 100+
Have you tried REPLACE ( StringExpression1 , StringExpression2 , StringExpression3 ) T-SQL function?
Dec 22 '06 #2
I tried something like this:

CAST(replace(m.Description, vlbf, ' ') AS VARCHAR(8000))AS "Product Description",

and tried variations of vlbf such as chr(10) chr(13) or \r\n and none of them worked.

I can't put them in single quotes as SQL will look for the actual occurence in the string rather than what it stands for.

I get the error column vlbf is unknown or chr(10) is an unknown function....
Dec 22 '06 #3
sorted it now..... used this in the end:

REPLACE(CAST(m.Description AS varChar(1000)),char(13) + char(10),' ') AS "Product Description",

Thanks for the help
Dec 22 '06 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

16
by: Eddie B. | last post by:
Does anyone know how to do Chr$(13) in .NET? Thanks, Eddie
6
by: C L Humphreys | last post by:
Hi, I'm trying to concatenate address fields and insert a LF&CR after each line. The SQL I use is based on select address1 & chr(10) & chr(13)& address2 & chr(10) & chr(13)& address3 & ...
1
by: jdph40 | last post by:
I am trying to enter text to look like paragraphs in a text box on a report, making it look like a memo. I enter (Chr$(13) & Chr$(10) & Chr$(10)) to insert a carriage return and 2 line breaks, but...
2
by: lauren quantrell | last post by:
Has anyone cunstructed a function to remove multiple carriage returns and replace it with a single return? Replace(mytext, Chr(13) & Chr(10) & Chr(13) & Chr(10), Chr(13) & Chr(10)) ... only...
2
by: MLH | last post by:
I run the following command in main menu form open event code... Me!MainMenuLabel.Caption = DLookup("", "qryAdminList") & Chr$(10) & Chr$(13) & DLookup("", "qryAdminList") & Chr$(10) & Chr$(13)...
2
by: crjunk | last post by:
I have the following code in my web page. Dim tmpReplace As String 'Giving tmpReplace the value from the textbox on the webform. tmpReplace = txtComments.Text.Trim 'Prevents report from...
10
by: Dan Nash | last post by:
Lo, In a previous version of an app, I used replace to convert multiline text boxes to something easier to put in a database, and then i used replace(chr(13), "<br>") when i needed to show the...
1
by: Felix | last post by:
We have a problem with the Chr(13) and WebServices (we are using VB.NET 2003). If we have a string that contains the Chr(13) and the Chr(10) (just the vbCrLf characters) and we pass it through a...
5
by: Computer Guru | last post by:
I have a form I'm using as a dialog box. It has default text that is two paragraphs. It has a function setText(byval text as string) that specifies the text in the read-only textbox that's on...
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.