If I want to replace a Carriage Return (denoted as 'CR' below), what would I use?

SELECT Cust_Name AS 'Name', Cust_Add1 AS 'Address', Replace(Cust_Add2, 'CR', ', ') + ' ' + Cust_Zip as 'TownStateZip' FROM Customers

Thank you

asked 18 Feb '17, 15:20

gchq's gravatar image

gchq
421263141
accept rate: 27%


Use the standard '\n' to represent CR.

permanent link

answered 18 Feb '17, 16:41

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

I tried that and it skipped over it.. So I have had to replace it at DataTable level..

Dim CityString As String = ReturnText(Row("CityStateZip"))
            CityString = CityString.Replace(vbCrLf, ", ").Replace(vbCr, "")

 Await Task.Run(Sub()
                           Using vService As New Service1Client
                               strSQL = "SELECT Cust_Name AS 'Name', Cust_Add1 AS 'Address', Replace(Cust_Add2, '/n', ', ') + ' ' + Cust_Zip as 'CityStateZip' FROM Customers"
                               Using DS As DataSet = vService.ReturnDataSet(strSQL, Current_HOA_ID)
                                   MainDT = DS.Tables(0).Copy
                               End Using
                           End Using
                       End Sub)
        If MainDT.Columns.Contains("Selected") = False Then
            With MainDT.Columns
                .Add("Selected", GetType(Boolean))
            End With
        End If

        For Each Row As DataRow In MainDT.Rows
            Row("Name") = ReturnText(Row("Name"))
            Row("Address") = ReturnText(Row("Address"))
            Row("CityStateZip") = ReturnText(Row("CityStateZip"))
            Dim CityString As String = ReturnText(Row("CityStateZip"))
            CityString = CityString.Replace(vbCrLf, ", ").Replace(vbCr, "")
            Row("CityStateZip") = CityString
            Row("Selected") = False
        Next
(18 Feb '17, 16:49) gchq
Replies hidden
1

Just noticed - had the slash the wrong way round! Good way to spend a Saturday going round in circles :-(

(18 Feb '17, 16:51) gchq
Comment Text Removed

The \n escape sequence represents \x0a which is a line feed. The carriage return character is \x0d. See ASCII codes here.

SELECT CAST ( '\n' AS BINARY );
'\x0A'    
------
0x0a 
(19 Feb '17, 08:15) Breck Carter

FWIW, here's more on the CR/LF discussion (as your VB.Net code does use both of them, as well):

How to make a new line within a string

(20 Feb '17, 02:48) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×243

question asked: 18 Feb '17, 15:20

question was seen: 3,141 times

last updated: 20 Feb '17, 02:48