1

How do I execute a sql query that extracts the ENTER [chr(13)+ char(10)] of characters in the sql string? THANKS.

AdiT
  • 51
  • 1
  • 8
  • 2
    Please add details about what you are trying to achieve, what have you tried so far. Your question in it current form is unclear, or can be simply answered by the following statement: "by pressing F5 in SSMS while the query window is open" – Pred Jul 04 '19 at 13:40
  • Possible duplicate of [UPDATE and REPLACE part of a string](https://stackoverflow.com/questions/17365222/update-and-replace-part-of-a-string) – Diado Jul 04 '19 at 13:47

1 Answers1

1

I assume you want to remove both chr(13) and chr(10) from a varchar column.
For that you can use the replace function

declare @test varchar(100) = 'hello' + char(13) + 'world'
select @test, 
       len(@test),
       replace(@test, char(13), ''),
       len(replace(@test, char(13), ''))

the result is

COLUMN1 COLUMN2 COLUMN3     COLUMN4 
------- ------- -------     ------- 
hello
world   11      helloworld  10

for both values it would be

select replace(replace(@test, char(13), ''), char(10), '')

EDIT
or better yet (thanks to @MichaelTobisch)

select REPLACE(@test, char(13) + char(10), '')
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • 1
    I guess that the question is meant for what is vbCrLf in Visual Basic ;-) - therefore SELECT REPLACE(fieldname, char(13) + char(10), '') should work and needs only one call of the REPLACE function. – Michael Tobisch Jul 04 '19 at 14:30