Usuń wszystkie spacje z ciągu znaków w programie SQL Server


222

Jaki jest najlepszy sposób na usunięcie wszystkich spacji z ciągu znaków w SQL Server 2008?

LTRIM(RTRIM(' a b ')) usuną wszystkie spacje po prawej i lewej stronie ciągu, ale muszę też usunąć spację na środku.


5
Przez „wszystkie białe spacje” rozumiesz tylko zwykłe spacje? Czy masz na myśli tabulatory, CR, LF i inne znaki, które mogą być wyświetlane jako białe znaki?
Gordon Linoff

3
@GordonLinoff: Miałem na myśli zwykłe spacje
Ananth

Odpowiedzi:


385

Wystarczy go wymienić;

SELECT REPLACE(fld_or_variable, ' ', '')

Edycja: tylko dla wyjaśnienia; jest globalnym zamiennikiem, nie ma potrzeby trim()martwić się o wiele spacji dla jednego charlub varchar:

create table #t (
    c char(8),
    v varchar(8))

insert #t (c, v) values 
    ('a a'    , 'a a'    ),
    ('a a  '  , 'a a  '  ),
    ('  a a'  , '  a a'  ),
    ('  a a  ', '  a a  ')

select
    '"' + c + '"' [IN], '"' + replace(c, ' ', '') + '"' [OUT]
from #t  
union all select
    '"' + v + '"', '"' + replace(v, ' ', '') + '"'
from #t 

Wynik

IN             OUT
===================
"a a     "     "aa"
"a a     "     "aa"
"  a a   "     "aa"
"  a a   "     "aa"
"a a"          "aa"
"a a  "        "aa"
"  a a"        "aa"
"  a a  "      "aa"

2
Co zrobić, jeśli między słowami jest wiele białych znaków? WYMIEŃ usuwa tylko jeden na raz. Należy napisać funkcję zdefiniowaną przez użytkownika, która usuwa wiele spacji.
Farhan,

Wydaje się, że to nie zastępuje końcowych białych spacji
Ryan Sampson

7
Powinien zastąpić wszystkie białe znaki wszędzie
Alex K.

Usunie wszystkie białe spacje oprócz spływu. Aby usunąć końcowe, dodaj TRIM (REPLACE (fld_or_variable, '', '')). Powodem, dla którego usuwa nawet wiele spacji, jest to, że ... cóż, zmieniają wszystkie znaki spacji na nic, nawet jeśli spacje są obok siebie, czy nie.
snaplemouton 18.04.13

32
Zamień usuwa wszystkie spacje, nawet jedno końcowe. Jeśli po wykonaniu tej operacji w danych nadal znajdują się spacje, prawdopodobnie nie są to spacje, ale znaki niedrukowalne, takie jak tabulatory lub znaki powrotu ładunku.
HLGEM


28

Jeśli jest to aktualizacja w tabeli, wystarczy uruchomić tę aktualizację wiele razy, aż wpłynie na 0 wierszy.

update tableName
set colName = REPLACE(LTRIM(RTRIM(colName)), '  ', ' ')
where colName like '%  %'


11

Referencje zaczerpnięte z tego bloga:

Najpierw utwórz przykładową tabelę i dane:

CREATE TABLE tbl_RemoveExtraSpaces
(
     Rno INT
     ,Name VARCHAR(100)
)
GO

INSERT INTO tbl_RemoveExtraSpaces VALUES (1,'I    am     Anvesh   Patel')
INSERT INTO tbl_RemoveExtraSpaces VALUES (2,'Database   Research and     Development  ')
INSERT INTO tbl_RemoveExtraSpaces VALUES (3,'Database    Administrator     ')
INSERT INTO tbl_RemoveExtraSpaces VALUES (4,'Learning    BIGDATA    and       NOSQL ')
GO

Skrypt do wyboru łańcucha bez dodatkowych spacji:

SELECT
     [Rno]
    ,[Name] AS StringWithSpace
    ,LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([Name],CHAR(32),'()'),')(',''),'()',CHAR(32)))) AS StringWithoutSpace
FROM tbl_RemoveExtraSpaces

Wynik:

Rno         StringWithSpace                                 StringWithoutSpace
----------- -----------------------------------------  ---------------------------------------------
1           I    am     Anvesh   Patel                      I am Anvesh Patel
2           Database   Research and     Development         Database Research and Development
3           Database    Administrator                       Database Administrator
4           Learning    BIGDATA    and       NOSQL          Learning BIGDATA and NOSQL

2
Czy zdajesz sobie sprawę, że OP chciał usunąć WSZYSTKIE spacje zamiast zastąpić wiele spacji jednym?
Kaii,

3
Powinno to zostać odrzucone, ponieważ odpowiedź nie jest zgodna z wymaganiami PO, ale zamiast tego otrzymała 4 głosy poparcia. Witamy w Stack Overflow.
Mr.J

@ Mr.J, a teraz ma 9. Szalony świat.
Leszek P

11

100% pracy

UPDATE table_name SET  "column_name"=replace("column_name", ' ', ''); //Remove white space

UPDATE table_name SET  "column_name"=replace("column_name", '\n', ''); //Remove newline

UPDATE table_name SET  "column_name"=replace("column_name", '\t', ''); //Remove all tab

Możesz użyć "column_name"lubcolumn_name

Dzięki

Subroto


2
Musiałem użyć, select replace(replace(replace(varcharColumn,char(13),''),char(10),''),' ','')aby usunąć wszystkie nowe wiersze i spacje z varcharkolumny. Jeśli użyłem „\ n” i „\ r” zamiast char (13) i char (10), to nie zadziałało.
stycznia

aby to wykonać, należy uruchomić „SET SQL_SAFE_UPDATES = 0;”
Shai Epstein


6

Jeśli w ciągu znajduje się wiele białych znaków, zastąpienie może nie działać poprawnie. W tym celu należy użyć następującej funkcji.

CREATE FUNCTION RemoveAllSpaces
(
    @InputStr varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @InputStr
while charindex(' ', @ResultStr) > 0
    set @ResultStr = replace(@InputStr, ' ', '')

return @ResultStr
END

Przykład:

select dbo.RemoveAllSpaces('aa  aaa       aa aa                 a')

Wynik:

aaaaaaaaaa

1
„@InputStr” powinno być „@ResultStr” w treści pętli while.
jjoelson

@jjoelson, czy mógłbyś stworzyć tę funkcję i przetestować ją przed wskazaniem błędów?
Farhan,

7
Przepraszam, pracowałem przy twoim powyższym założeniu, że REPLACE usuwa tylko jeden na raz, w takim przypadku funkcja ta spowodowałaby nieskończoną pętlę dla danych wejściowych z wieloma spacjami. W rzeczywistości pętla while nie jest wcale potrzebna.
jjoelson

2
Farhan, jeśli REPLACE działało tak, jak myślisz, to pierwszy komentarz @ jjoelson jest poprawny. W przeciwnym razie będziesz mieć nieskończoną pętlę, ponieważ InputStr nigdy się nie zmienia, więc ResultStr zawsze będzie pierwszym wynikiem WYMIANY. Przyczyną tego, że Twój kod działa, jest to, że WYMIANA jest potrzebna tylko 1 raz. Nigdy nie jest wywoływany po raz drugi, bez względu na to, jaką strunę na niego rzucisz. Dodaj licznik i wydrukuj go przy każdej iteracji. Zawsze będzie wynosić 1. REPLACE (InputStr, '', '') usunie wszystkie spacje za pomocą jednego wywołania.
Gilbert

6

To polega na usunięciu spacji na ciągach:

UPDATE
    tablename
SET
    columnname = replace(columnname, ' ', '');

2

Na wszelki wypadek TRIM spacji we wszystkich kolumnach możesz użyć tego skryptu, aby zrobić to dynamicznie:

--Just change table name
declare @MyTable varchar(100)
set @MyTable = 'MyTable'

--temp table to get column names and a row id
select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.COLUMNS 
WHERE   DATA_TYPE IN ('varchar', 'nvarchar') and TABLE_NAME = @MyTable

declare @tri int
select @tri = count(*) from #tempcols
declare @i int
select @i = 0
declare @trimmer nvarchar(max)
declare @comma varchar(1)
set @comma = ', '

--Build Update query
select @trimmer = 'UPDATE [dbo].[' + @MyTable + '] SET '

WHILE @i <= @tri 
BEGIN

    IF (@i = @tri)
        BEGIN
        set @comma = ''
        END
    SELECT  @trimmer = @trimmer + CHAR(10)+ '[' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))'+@comma
    FROM    #tempcols
    where id = @i

    select @i = @i+1
END

--execute the entire query
EXEC sp_executesql @trimmer

drop table #tempcols

2

jeśli chcesz usunąć spacje, - i inny tekst z ciągu, użyj następujących poleceń:

załóżmy, że masz w tabeli numer telefonu komórkowego, np. „718-378-4957” lub „7183784957”, i chcesz go zastąpić i uzyskać numer telefonu komórkowego, a następnie użyć następującego tekstu.

select replace(replace(replace(replace(MobileNo,'-',''),'(',''),')',''),' ','') from EmployeeContactNumber

Wynik: - 7183784957


2

Tylko wskazówka, na wypadek problemów z funkcją zamiany, możesz ustawić typ danych na nchar (w takim przypadku jest to stała długość i nie będzie działać).


2

Aby wszystkie powyższe odpowiedzi były kompletne, na StackOverflow znajdują się dodatkowe posty dotyczące postępowania ze WSZYSTKIMI znakami spacji ( pełna lista tych znaków znajduje się na stronie https://en.wikipedia.org/wiki/Whitespace_character ):



2

jest to dla mnie przydatne:

CREATE FUNCTION dbo.TRIM(@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
    RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,CHAR(10),'[]'),CHAR(13),'[]'),char(9),'[]'),CHAR(32),'[]'),'][',''),'[]',CHAR(32))));
END
GO

.


1

Miałem ten problem dzisiaj i zamieniłem / przycinałem załatwiłem sprawę ... patrz poniżej.

update table_foo 
set column_bar  = REPLACE(LTRIM(RTRIM(column_bar)), '  ', '')

przed i po :

old-bad:  column_bar    |   New-fixed:   column_bar
       '  xyz  '        |                'xyz'   
       '  xyz  '        |                'xyz' 
       '  xyz  '        |                'xyz' 
       '  xyz  '        |                'xyz' 
       '  xyz  '        |                'xyz' 
       '  xyz  '        |                'xyz' 

1
Po co męczyć się z LTRIM& RTRIM?
BanksySan

Chłodny. Czy masz przykład?
BanksySan

0

Aby usunąć spacje w ciągu po lewej i prawej stronie. Aby usunąć miejsce w środkowym użyciuReplace .

Możesz użyć, RTRIM()aby usunąć spacje z prawej strony i LTRIM()aby usunąć spacje z lewej, stąd lewe i prawe spacje zostały usunięte w następujący sposób:

SELECT * FROM table WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("Bob alias baby"))

0

Składnia do zamiany określonych znaków:

REPLACE ( string_expression , string_pattern , string_replacement )  

Na przykład w ciągu „HelloReplaceThingsGoing” Zastąp słowo jest zastąpione przez How

SELECT REPLACE('HelloReplaceThingsGoing','Replace','How');
GO

0

Wersja funkcjonalna (udf), która usuwa spacje, cr, lf, tabulatory lub konfigurowalne.

select Common.ufn_RemoveWhitespace(' 234   asdf   wefwef 3  x   ', default) as S

Wynik: „234asdfwefwef3x”

alter function Common.RemoveWhitespace
(
    @pString nvarchar(max),
    @pWhitespaceCharsOpt nvarchar(max) = null -- default: tab, lf, cr, space 
)  
returns nvarchar(max) as
/*--------------------------------------------------------------------------------------------------
    Purpose:   Compress whitespace

    Example:  select Common.ufn_RemoveWhitespace(' 234   asdf   wefwef 3  x   ', default) as s 
              -- Result: 234asdfwefwef3x

    Modified    By          Description
    ----------  ----------- --------------------------------------------------------------------
    2018.07.24  crokusek    Initial Version 
  --------------------------------------------------------------------------------------------------*/ 
begin    
    declare 
        @maxLen bigint = 1073741823, -- (2^31 - 1) / 2 (https://stackoverflow.com/a/4270085/538763)
        @whitespaceChars nvarchar(30) = coalesce(
            @pWhitespaceCharsOpt, 
            char(9) + char(10) + char(13) + char(32));  -- tab, lf, cr, space

    declare
        @whitespacePattern nvarchar(30) = '%[' + @whitespaceChars + ']%',
        @nonWhitespacePattern nvarchar(30) = '%[^' + @whitespaceChars + ']%',
        @previousString nvarchar(max) = '';

    while (@pString != @previousString)
    begin
        set @previousString = @pString;

        declare
            @whiteIndex int = patindex(@whitespacePattern, @pString);

        if (@whiteIndex > 0)
        begin                   
            declare 
                @whitespaceLength int = nullif(patindex(@nonWhitespacePattern, substring(@pString, @whiteIndex, @maxLen)), 0) - 1;                

            set @pString = 
                substring(@pString, 1, @whiteIndex - 1) + 
                iif(@whiteSpaceLength > 0, substring(@pString, @whiteIndex + @whiteSpaceLength, @maxLen), '');
        end        
    end        
    return @pString;
end
go

0

Z jakiegoś powodu zamiana działa tylko z jednym ciągiem znaków za każdym razem. Miałem taki ciąg „ Test MSP ” i chcę zostawić tylko jedno miejsce.

Użyłem podejścia, które zrobił @Farhan, ale z pewnymi modyfikacjami:

CREATE FUNCTION ReplaceAll
(
    @OriginalString varchar(8000),
    @StringToRemove varchar(20),
    @StringToPutInPlace varchar(20)
)
RETURNS varchar(8000)
AS
BEGIN
declare @ResultStr varchar(8000)
set @ResultStr = @OriginalString
while charindex(@StringToRemove, @ResultStr) > 0
    set @ResultStr = replace(@ResultStr, @StringToRemove, @StringToPutInPlace)

return @ResultStr
END

Następnie uruchamiam moją aktualizację w ten sposób

UPDATE tbTest SET Description = dbo.ReplaceAll(Description, '  ', ' ') WHERE ID = 14225

Potem otrzymałem ten wynik: Test MSP

Publikowanie tutaj, jeśli ktoś będzie tego potrzebował tak jak ja.

Działający na: Microsoft SQL Server 2016 (SP2)


0

Sprawdź i wypróbuj poniższy skrypt (Testowany w jednostce) -

--Declaring
DECLARE @Tbl TABLE(col_1 VARCHAR(100));

--Test Samples
INSERT INTO @Tbl (col_1)
VALUES
('  EY     y            
Salem')
, ('  EY     P    ort       Chennai   ')
, ('  EY     Old           Park   ')
, ('  EY   ')
, ('  EY   ')
,(''),(null),('d                           
    f');

SELECT col_1 AS INPUT,
    LTRIM(RTRIM(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(col_1,CHAR(10),' ')
        ,CHAR(11),' ')
        ,CHAR(12),' ')
        ,CHAR(13),' ')
        ,CHAR(14),' ')
        ,CHAR(160),' ')
        ,CHAR(13)+CHAR(10),' ')
    ,CHAR(9),' ')
    ,' ',CHAR(17)+CHAR(18))
    ,CHAR(18)+CHAR(17),'')
    ,CHAR(17)+CHAR(18),' ')
    )) AS [OUTPUT]
FROM @Tbl;

-1

Wygląda na to, że wszyscy odnoszą się do jednej funkcji WYMIANY. Lub nawet wiele wywołań funkcji REPLACE. Ale jeśli masz dynamiczne wyjście z nieznaną liczbą spacji, to nie zadziała. Każdy, kto regularnie zajmuje się tym problemem, wie, że REPLACE usunie tylko jedno miejsce, NIE WSZYSTKO, jak powinno. Wydaje się, że LTRIM i RTRIM mają ten sam problem. Pozostaw to Microsoft. Oto przykładowe dane wyjściowe, które wykorzystują pętlę WHILE do usuwania wartości ALL CHAR (32) (spacja).

DECLARE @INPUT_VAL  VARCHAR(8000)
DECLARE @OUTPUT_VAL VARCHAR(8000)

SET @INPUT_VAL = '      C               A                         '
SET @OUTPUT_VAL = @INPUT_VAL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
    SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END

PRINT 'START:' + @INPUT_VAL + ':END'
PRINT 'START:' + @OUTPUT_VAL + ':END'

Oto wynik powyższego kodu:

START:      C               A                         :END
START:CA:END

Teraz, aby pójść o krok dalej i wykorzystać go w instrukcji UPDATE lub SELECT, zmień go na udf.

CREATE FUNCTION udf_RemoveSpaces (@INPUT_VAL    VARCHAR(8000))
RETURNS VARCHAR(8000)
AS 
BEGIN

DECLARE @OUTPUT_VAL VARCHAR(8000)
SET @OUTPUT_VAL = @INPUT_VAL
-- ITTERATE THROUGH STRING TO LOOK FOR THE ASCII VALUE OF SPACE (CHAR(32)) REPLACE IT WITH BLANK, NOT NULL
WHILE CHARINDEX(CHAR(32), @OUTPUT_VAL) > 0 BEGIN
    SET @OUTPUT_VAL = REPLACE(@INPUT_VAL, CHAR(32), '')
END

RETURN @OUTPUT_VAL
END

Następnie użyj funkcji w instrukcji SELECT lub INSERT:

UPDATE A
SET STATUS_REASON_CODE = WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE

INSERT INTO SOMETABLE
(STATUS_REASON_CODE)
SELECT WHATEVER.dbo.udf_RemoveSpaces(STATUS_REASON_CODE)
FROM WHATEVER..ACCT_INFO A
WHERE A.SOMEVALUE = @SOMEVALUE

1
„WYMIANA usunie tylko jedną spację”. ...naprawdę? Ta prosta wersja demonstracyjna nie sugeruje: dbfiddle.uk/… . Czy masz przykład, gdzie to nie zadziała?
ADyson
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.