Replace new line (carriage return) in string (varchar)

[former_SQLA_member1694879]

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

[MarkCulp · Accepted solution]

Use the standard '\\n' to represent CR.

[former_SQLA_member1694879 · Accepted solution]

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

[former_SQLA_member1694879 · Accepted solution]

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

[Breck_Carter · Accepted solution]

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

SELECT CAST ( '\\n' AS BINARY );
'\
'    
------
0x0a 

[VolkerBarth · Accepted solution]

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