DB研討會 : [轉貼]去除 MS SQL 欄位中空白

select REPLACE(PnameD,' ','')
from Prod

RTRIM ( character_expression )

character_expression

SELECT RTRIM('Removes trailing spaces.   ');

Removes trailing spaces.

SELECT RTRIM('Four spaces are after the period in this sentence.    ') + 'Next string.';

Four spaces are after the period in this sentence.Next string.

DECLARE @string_to_trim varchar(60);
SET @string_to_trim = 'Four spaces are after the period in this sentence.    ';
SELECT @string_to_trim + ' Next string.';
SELECT RTRIM(@string_to_trim) + ' Next string.';
GO

SQL

-------------------------------------------------------------------------
Four spaces are after the period in this sentence.     Next string.

(1 row(s) affected)`

-------------------------------------------------------------------------
Four spaces are after the period in this sentence. Next string.

(1 row(s) affected)

LTRIM ( character_expression )

character_expression

SQL

SELECT LTRIM('     Five spaces are at the beginning of this string.') FROM sys.databases;

---------------------------------------------------------------
 Five spaces are at the beginning of this string.

DECLARE @string_to_trim varchar(60);
SET @string_to_trim = '     5 spaces are at the beginning of this string.';
SELECT
    @string_to_trim AS 'Original string',
    LTRIM(@string_to_trim) AS 'Without spaces';
GO

Original string Without spaces
--------------------------------------------------- ---------------------------------------------
     5 spaces are at the beginning of this string.  5 spaces are at the beginning of this string.

-- Syntax for SQL Server and Azure SQL Database
TRIM ( [ characters FROM ] string )

-- Syntax for Azure SQL Data Warehouse
TRIM ( string )

SQL

SELECT TRIM( '     test    ') AS Result;

test

SQL

SELECT TRIM( '.,! ' FROM  '#     test    .') AS Result;

# test