SQL Server MSSQL remove enter space only with empty row in one cell

pod7payv 于 2023-02-18 发布在其他

关注(0)|答案(1)|浏览(89)

I have a table in Excel. This table I load to MSSQL through SSIS. In the table is column TEXT that contains multiple sentences in one cell separated by enter spaces (alt + enter). My table looks like this:
| NAME | ID | TEXT |
| ------------ | ------------ | ------------ |
| JOHN SMITH | 125 | TEXT TEXT TEXT |

But sometimes I have in column TEXT more enter spaces without text, like this:

NAME	ID	TEXT
JOHN SMITH	125	TEXT TEXT TEXT

I need to remove excess line breaks but keep enter space after each TEXT.

sql-server

来源：https://stackoverflow.com/questions/75459114/mssql-remove-enter-space-only-with-empty-row-in-one-cell

1条答案

按热度按时间

kg7wmglp1#

In basic case you can use REPLACE function for replace duplicates

UPDATE T SET T.T = REPLACE(T.T, CHAR(10) + CHAR(10), CHAR(10))
WHERE T.T LIKE('%' + CHAR(10) + CHAR(10) + '%')

But this query replace ONLY duplicates, so in case 3 or more duplicates you need repeat the query. For automate this you can use PROCEDURE like next:

CREATE PROCEDURE cleanDupes
AS
BEGIN
    DECLARE @count INT
    SET @count = 1
    WHILE @count > 0
    BEGIN
        UPDATE T SET T.T = REPLACE(T.T, CHAR(10) + CHAR(10), CHAR(10))
        WHERE T.T LIKE('%' + CHAR(10) + CHAR(10) + '%')
        SET @count = (SELECT COUNT(*) FROM T WHERE T.T LIKE('%' + CHAR(10) + CHAR(10) + '%'))
    END
    SELECT * FROM T
END
GO

Test this solution on https://sqlize.online/sql/mssql2019/3ce3f73e8d27862fd7d268523a4a4a14/

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server MSSQL remove enter space only with empty row in one cell

1条答案

相关问题

热门标签

最新问答