SQL Server: How to perform Rtrim on all varchar columns of a table
For a generic approach, you can use a script like this to generate the statement for you, for a given table (useful if you have many columns!):
DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'YourTableName'
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=RTRIM([' + COLUMN_NAME + '])'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND DATA_TYPE = 'varchar'
SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
PRINT @SQL
That will just print the SQL statement out. You can either then copy + run the statement, or just EXECUTE(@SQL). This is untested, so just try it out on a test table first :)
Trim all database fields
No cursors. Copy and paste the output. Works also for SQL 2000, which doesn't have varchar(max). This can be easily extended to add a GO line to the end of each UPDATE if desired.
SELECT SQL
FROM ( SELECT t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
, 0 SORT
, 'UPDATE ' + QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) SQL
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar')
GROUP BY t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
UNION ALL
SELECT x.TABLE_CATALOG
, x.TABLE_SCHEMA
, x.TABLE_NAME
, CASE WHEN x.COLUMN_NAME_MIN = y.COLUMN_NAME
THEN 1
ELSE 2
END SORT
, CASE WHEN x.COLUMN_NAME_MIN = y.COLUMN_NAME
THEN 'SET '
ELSE ' , '
END + y.SQL SQL
FROM ( SELECT t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
, MIN(c.COLUMN_NAME) COLUMN_NAME_MIN
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar')
GROUP BY t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
) x
JOIN ( SELECT t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
, c.COLUMN_NAME
, QUOTENAME(c.COLUMN_NAME) + ' = LTRIM(RTRIM(' + QUOTENAME(c.COLUMN_NAME) + '))' SQL
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar')
) y
ON x.TABLE_CATALOG = y.TABLE_CATALOG
AND x.TABLE_SCHEMA = y.TABLE_SCHEMA
AND x.TABLE_NAME = y.TABLE_NAME
) x
ORDER BY x.TABLE_CATALOG
, x.TABLE_SCHEMA
, x.TABLE_NAME
, x.SORT
, x.SQL
Remove Trailing Spaces and Update in Columns in SQL Server
Try SELECT LTRIM(RTRIM('Amit Tech Corp '))
LTRIM - removes any leading spaces from left side of string
RTRIM - removes any spaces from right
Ex:
update table set CompanyName = LTRIM(RTRIM(CompanyName))
Is there a way to TRIM all data in a SELECT * FROM statement?
You need to specify each string column by hand:
SELECT TRIM(col1), --LTRIM(RTRIM(...)) If RDBMS is SQL Server
TRIM(col2),
TRIM(col3),
TRIM(col4)
-- ...
FROM table
There is another problem with your proposal. * is placeholder for each column in table so there will be problem with trimming date/decimal/spatial data ....
Addendum
Using Oracle 18c Polymorphic Table Functions(provided code is just PoC, there is a space for a lot of improvements):
CREATE TABLE tab(id INT, d DATE,
v1 VARCHAR2(100), v2 VARCHAR2(100), v3 VARCHAR2(100) );
INSERT INTO tab(id, d,v1, v2, v3)
VALUES (1, SYSDATE, ' aaaa ', ' b ', ' c');
INSERT INTO tab(id, d,v1, v2, v3)
VALUES (2, SYSDATE+1, ' afasd', ' ', ' d');
COMMIT;
SELECT * FROM tab;
-- Output
.----.-----------.-----------.-----------.-----.
| ID | D | V1 | V2 | V3 |
:----+-----------+-----------+-----------+-----:
| 1 | 02-MAR-18 | aaaa | b | c |
:----+-----------+-----------+-----------+-----:
| 2 | 03-MAR-18 | afasd | | d |
'----'-----------'-----------'-----------'-----'
And table function:
CREATE OR REPLACE PACKAGE ptf AS
FUNCTION describe(tab IN OUT dbms_tf.table_t)RETURN dbms_tf.describe_t;
PROCEDURE FETCH_ROWS;
END ptf;
/
CREATE OR REPLACE PACKAGE BODY ptf AS
FUNCTION describe(tab IN OUT dbms_tf.table_t) RETURN dbms_tf.describe_t AS
new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
FOR i IN 1 .. tab.column.count LOOP
IF tab.column(i).description.type IN ( dbms_tf.type_varchar2) THEN
tab.column(i).pass_through:=FALSE;
tab.column(i).for_read:= TRUE;
NEW_COLS(i) :=
DBMS_TF.COLUMN_METADATA_T(name=> tab.column(i).description.name,
type => tab.column(i).description.type);
END IF;
END LOOP;
RETURN DBMS_TF.describe_t(new_columns=>new_cols, row_replication=>true);
END;
PROCEDURE FETCH_ROWS AS
inp_rs DBMS_TF.row_set_t;
out_rs DBMS_TF.row_set_t;
rows PLS_INTEGER;
BEGIN
DBMS_TF.get_row_set(inp_rs, rows);
FOR c IN 1 .. inp_rs.count() LOOP
FOR r IN 1 .. rows LOOP
out_rs(c).tab_varchar2(r) := TRIM(inp_rs(c).tab_varchar2(r));
END LOOP;
END LOOP;
DBMS_TF.put_row_set(out_rs, replication_factor => 1);
END;
END ptf;
And final call:
CREATE OR REPLACE FUNCTION trim_col(tab TABLE)
RETURN TABLE pipelined row polymorphic USING ptf;
SELECT *
FROM trim_col(tab); -- passing table as table function argument
.----.-----------.-------.-----.----.
| ID | D | V1 | V2 | V3 |
:----+-----------+-------+-----+----:
| 1 | 02-MAR-18 | aaaa | b | c |
:----+-----------+-------+-----+----:
| 2 | 03-MAR-18 | afasd | - | d |
'----'-----------'-------'-----'----'
db<>fiddle demo
Is it possible to Trim all the values in a column in a single statement?
MS SQL does not have a trim function. You'll need to use rTrim and lTrim together.
update MyTable set Name = lTrim(rTrim(name))
How to apply a function to all fields (all columns of all rows)
Here is one way you can do this. It is going to require some dynamic sql no matter you slice this because you have to dynamically get the column names.
declare @SQL nvarchar(max)
select @SQL = 'Update YourTable set ' + STUFF((select ', replace(' + QUOTENAME(c2.name) + ', ''"'', '''')'
from sys.columns c2
where object_id = object_id('YourTable')
order by c2.column_id
for xml path('')), 1, 1, '')
from sys.columns
where object_id = object_id('YourTable')
group by object_id
select @SQL
--exec sp_executesql @SQL --You can uncomment this when you comfortable with the output
Trim Spaces inside whole Database
Try below one
DECLARE @SQL AS VarChar(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'UPDATE T SET T.'+IC.COLUMN_NAME +
' = LTRIM(RTRIM(' + IC.COLUMN_NAME+'))
FROM '+ IT.TABLE_SCHEMA + '.[' + IT.TABLE_NAME +
'] AS T ;' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES IT
JOIN INFORMATION_SCHEMA.COLUMNS IC ON IT.TABLE_NAME = IC.TABLE_NAME
AND IT.TABLE_SCHEMA = IC.TABLE_SCHEMA AND IT.TABLE_TYPE = 'BASE TABLE'
and IC.DATA_TYPE in( 'varchar','char','nvarchar','nchar')
Exec (@SQL)
How can I search all columns in a table?
SELECT ...
FROM yourtable
WHERE 'val' IN (field1, field2, field3, field4, ...)
if you're looking for exact full-field matches. If you're looking for substring matches, you'll have to go about it the long way:
WHERE field1 LIKE '%val%' or field2 LIKE '%val%' etc....
Related Topics
Formatting Clear and Readable SQL Queries
Does Introducing Foreign Keys to MySQL Reduce Performance
What Is Db/Development_Structure.SQL in a Rails Project
Is Cut() Style Binning Available in Dplyr
T-Sql Insert into with Left Join
Aggregate Function Over a Given Time Interval
How to Convert Integer to Decimal in SQL Server Query
Blank Out Duplicate Column Values in SQL Reporting Services
Create View' Must Be The First Statement in a Query Batch
Sql Server 2005 Unique Constraint on Two Columns
How to Set a Datetime Variable in SQL Server 2008
Multiple Inner Join from The Same Table
Oracle SQL: Understanding the Behavior of Sys_Guid() When Present in an Inline View
Remove Extensions from Filename
How to Detect and Remove a Column That Contains Only Null Values