Posts Tagged ‘SQL Tips and Tricks’

A lot of you might have come across this problem just like me.


Firstly we create a temporary table.

create table #temp1


            id Int Identity(1,1),

            invoice varchar (20)


insert into #temp1 values (‘CR’)

insert into #temp1 values (‘CR ‘)

insert into #temp1 values (‘ CR ‘)

insert into #temp1 values (‘ CR’)


Select *  From #temp1

            where DATALENGTH(invoice) <> len(invoice)


The len function in SQL gives you the length of the characters including the spaces white DataLength excludes the trailing not preceding spaces.


Here if you don’t have the column defined as varchar rather you have an nvarchar field then you simply convert then to varchar before calling the respective functions like this.


Select *  From #temp1

            where DATALENGTH(convert(varchar(20),invoice)) <> len(convert(varchar(20),invoice))



Happy searching trailing spaces in your db, please share with us if you have better options.


Read Full Post »