Display all records with leading or trailing spaces

Question

I am looking for a SQL query (Using SQL 2008) to search various fields that I specify (or even all fields in particular tables) for leading or trailing spaces. I would like the output to be two columns with the following data:

ID Number, Data (that has the space), Field Name

Thanks for the help as usual!

I have, but I want to do this for multiple fields at the same time — user3513237, Jun 13 '15 at 03:17
This question is different from what you are asking in the comment. I would suggest that you ask *another* question with sample data and desired results, one that better represents wha t you are really looking for. If you modify this question, you will likely invalidate the answers already given, and that might attract downvotes. Try asking another question. — Gordon Linoff, Jun 13 '15 at 03:20

score 15 · Answer 1 · answered Jun 12 '15 at 23:04

15

You can use DATALENGTH

SELECT ID, Data, FieldName
FROM table
WHERE DATALENGTH(RTRIM(LTRIM(Data))) <> DATALENGTH(Data)

answered Jun 12 '15 at 23:04

freakinthesun

699
1
9
19

According to https://stackoverflow.com/questions/2552407/sql-server-2008-how-to-find-trailing-spaces#comment63658252_22745261 this may not work as intended for nvarchar columns. – kh42874 Dec 01 '21 at 16:55

score 3 · Answer 2 · answered Jun 12 '15 at 22:57

3

Try:

select `ID Number`, `Data (that has the space)`, `Field Name` from tbl WHERE data like ' %' or data like '% '

answered Jun 12 '15 at 22:57

hd1

33,938
5
80
91

score 1 · Answer 3 · answered Oct 16 '18 at 13:56

1

Try this.. this worked for me..It will find the records leading with special characters also

SELECT * FROM ITEM_MASTER WHERE BARCODE LIKE '[^0-9a-zA-Z ]%'

answered Oct 16 '18 at 13:56

Prince Prasad

1,528
1
16
20

Holmes IV · Answer 4 · 2015-06-15T16:29:20.653

I guess it depends exactly what you mean,by list of fields, the below code will do one at a time. However, if you want to give a list of 20 columns and check each, and output to a single list, that would take some recursion or some copy/paste unions.

select ID, DATA,Field_Name
from table
where DATA like '[ ]%'
        or DATA like '%[ ]'

The below will get you everything you are looking for with the exception of the ID. I wasn't sure if this was the column name or row or an Auto_ID or what. If its different in every table its a little tricky, but if its the same, you should be able to amend the below.

Declare @Tables Varchar(8000)
        ,@Columns Varchar(8000)
        ,@Dynamic_SQL NVARCHAR(MAX)


IF OBJECT_ID('tempdb..#TempTables') IS NOT NULL
    DROP TABLE #TempTables

Create Table #TempTables
(TableNames Varchar(8000))

Insert Into #TempTables
Values ('Finance_LTD_Summary_Data')
        ,('Golf_TX_TMP')


IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

Create Table #Results
(TableNames Varchar(8000)
    ,ID Varchar(8000)
    ,Data Varchar(8000)
    ,FieldName Varchar(8000))

IF OBJECT_ID('tempdb..#Fields') IS NOT NULL
    DROP TABLE #Fields

Create Table #Fields
(COLUMN_Name Varchar(8000))

DECLARE KeyTables_cursor CURSOR FOR 
        SELECT TableNames
            from #TempTables

        OPEN KeyTables_cursor

        FETCH NEXT FROM KeyTables_cursor
        INTO @Tables


        WHILE @@fetch_status = 0
        BEGIN   


                Set @Dynamic_SQL = 'truncate table #Fields

                                    Insert into #Fields(COlumn_Name)
                                    select COLUMN_NAME
                                    from INFORMATION_SCHEMA.COLUMNS
                                    where TABLE_NAME =''' + @Tables + ''''

                print convert(text,@Dynamic_Sql)                        
                exec sp_executesql @Dynamic_Sql


                DECLARE KeyColumns_cursor CURSOR FOR 
                SELECT COLUMN_Name
                    from #Fields

                OPEN KeyColumns_cursor

                FETCH NEXT FROM KeyColumns_cursor
                INTO @Columns


                WHILE @@fetch_status = 0
                BEGIN   

                    Set @Dynamic_SQL = 'Insert into #results(TableNames,Data,FieldName)
                                            Select ''' + @Tables + ''' , ' + @Columns + ' , ''' + @Columns + ''' 
                                                From ' + @Tables + '
                                                Where ' + @Columns + ' like ''[ ]%''
                                                        or ' + @Columns + ' like ''%[ ]'''


                    print convert(text,@Dynamic_Sql)
                    exec sp_executesql @Dynamic_Sql

                FETCH NEXT FROM KeyColumns_cursor into @Columns

                END

                CLOSE KeyColumns_cursor;
                DEALLOCATE KeyColumns_cursor;   


        FETCH NEXT FROM KeyTables_cursor into @Tables

        END

        CLOSE KeyTables_cursor;
        DEALLOCATE KeyTables_cursor;

I would want to provide a list of field names so I don't have to have as many queries — user3513237, Jun 13 '15 at 03:17

Display all records with leading or trailing spaces

4 Answers4