SQL Server database removes field cells, newline characters, carriage returns (using the Replace statement)

You can use the Replace function in SQL to replace certain characters in a field with the following syntax: Syntax replace (original-string, search-string, replace-string) parameter if one of the arguments is NULL , this function returns NULL. Original-string the string to be searched for. can be any length. Search-string the string to be searched for and replaced by Replace-string. The length of the string should not exceed 255 bytes. If Search-string is an empty string, the original string is returned as-is. Replace-string The string is used to replace the search-string. can be any length. If Replacement-string is an empty string, all occurrences of the search-string are deleted. Example: UPDATE tableName set Recordname=replace (Recordname, ' abc ', ' DDD ') replaces ABC in recordname field in table TableName with DDD One of the disadvantages of this function is that the substitution of the Text,ntext type field is not supported and can be implemented by the following statement: Update tableName set Recordname=replace (recordname as varchar (8000 ), ' abc ', ' DDD ')

2. NewLine characters in replace field, carriage return

1: Carriage return character

SELECT *, replace (detail, CHAR (+), ' <br> ') as displays the replaced content from Loginfo

2: Line break

SELECT *, replace (detail, CHAR (Ten), ' <br> ') as displays the replaced content from Loginfo

3: Return line break

SELECT *, replace (detail, char (+) + char (Ten), ' <br> ') as displays the replaced content from Loginfo

4: Replace carriage return line break to <BR><BR>

UPDATE loginfo SET detail = REPLACE (detail, char) + char (Ten), ' <br><br> ')

Note In the DOS \win series there will be a carriage return + newline character CHAR (10) +char

In UNIX \ Linux is a line break

Convert just remove the carriage return char (13).

The following SQL statement

UPDATE loginfo SET detail = REPLACE (detail, CHAR (13), ")

Replace the regular content

Update user_content Set [Address]=substring ([Address],1,patindex ('%<% ', [Address])-2) where PATINDEX ('%<% ', [ Address]) >2

SQL Server database removes field cells, newline characters, carriage returns (using the Replace statement)