SQL Server - Detect Trailing Spaces in a WHERE clause

Asked By Lesli on 29-Aug-07 11:26 AM
I need to be able to detect the difference between '3020' and '3020 ' in a
select statement WHERE clause.  I have tried "CONTAINS" but get the error
'Cannot use a CONTAINS or FREETEXT predicate on table 'SalesOrder' because it
is not full-text indexed.'  I do not have the ability to change the table
settings.

I need to be able to differentiate between the records with and without the
trailing space.

This is the select statement I'm using:

select SOM_SalesORderID, * from SalesOrder
where SOM_SalesORderID like '3020 %'

Any help would be appreciated.

Thanks,
Leslie




MH replied on 29-Aug-07 12:02 PM
What exactly are you trying to do?


Thats easy, the ones with the space at the end are different to the ones
without a space at the end.

MH
TheSQLGuru replied on 29-Aug-07 12:31 PM
Depending on column data types and database settings about padding, you may
not be able to accomplish your wish.  Sorry.  See BOL for information on
trailing space padding/removal.

--
TheSQLGuru
President
Indicium Resources, Inc.
Lesli replied on 29-Aug-07 12:36 PM
SQL doesn't see a difference between '3020' and '3020 ' in the WHERE clause
of a select statement.  We are simply trying to verify data and give
ourselves an "out" before running an update statement.



If you use: WHERE <FIELDNAME> = '3020'
or WHERE <FIELDNAME> = '3020 ', it returns the same results, even tho' some
records have the trailing spaces and some don't.

Leslie
M A Srinivas replied on 29-Aug-07 12:37 PM
CONTAINS works only with full text . Is the data type of
SOM_SalesORderID is char or varchar ?
You can use  ,

select SOM_SalesORderID, * from SalesOrder
where SOM_SalesORderID like '3020 %'

If you  want to check for '3020 ' (with space(s))
Lesli replied on 29-Aug-07 01:28 PM
The field in question is Nvarchar

Again, SQL doesn't detect the difference between
WHERE <FIELDNAME> = '3020'
and
WHERE <FIELDNAME> = '3020 '

Would counting the characters in the field work since it will always be
either 4 or 5 characters?

Leslie
Roy Harvey replied on 29-Aug-07 01:40 PM
AND DATALENGTH(SalesORderID) = DATALENGTH(N'3020 ')

Roy Harvey
Beacon Falls, CT
SpamFreePleas replied on 29-Aug-07 01:48 PM
If that column is a varchar this case statement should work:

SELECT *,
CASE
WHEN [SOM_SalesORderID] LIKE '% '
THEN 'With'
WHEN [SOM_SalesORderID] = '3020'
THEN 'Without'
END AS [Answer]
FROM dbo.SaleOrder
WHERE [SOM_SalesORderID] LIKE '3020%'
--
Take Care & God Bless ~ SPARKER ~
SpamFreePleas replied on 29-Aug-07 01:56 PM
Your original request of it being in the where clause is simply handled by
filtering your criteria on the newly create return column called [Answer] a
couple of examples of this would be as follows:

Example #1
___________________________________________________________________
SELECT *
FROM dbo.SaleOrder
WHERE [SOM_SalesORderID] LIKE '3020%' AND
CASE
WHEN [SOM_SalesORderID] LIKE '% '
THEN 'With'
WHEN [SOM_SalesORderID] = '3020'
THEN 'Without'
END = 'With'
___________________________________________________________________
Example #2
___________________________________________________________________
SELECT * FROM (
SELECT *,
CASE
WHEN [SOM_SalesORderID] LIKE '% '
THEN 'With'
WHEN [SOM_SalesORderID] = '3020'
THEN 'Without'
END AS [Answer]
FROM dbo.SaleOrder
WHERE [SOM_SalesORderID] LIKE '3020%') AS DAT
WHERE DAT.[Answer] = 'With'
___________________________________________________________________

--
Take Care & God Bless ~ SPARKER ~
Russell Fields replied on 29-Aug-07 02:02 PM
Leslie,

Well, you have stirred up some interest.  One issue that TheSQLGuru raised
was the ANSI_PADDING setting when your table and columns were created.

But I also tried this little script:

create table #george (id int identity, num nvarchar(10))
go
insert into #george (num) values ('1234')
insert into #george (num) values ('1234 ')
go
-- Returns IDs 1 and 2
select * from #george where num = '1234'
-- Returns ID 2
select * from #george where num like '1234 %'
go
drop table #george
go

Apparently a direct column compare ignores trailing blanks, but a LIKE can
see them, if properly specified. Unfortunately for understanding your
problem, I get the same results if I CREATE TABLE with SET ANSI_PADDING ON
or OFF.  And I get the same results whether comparing with '1234 %' or
N'1234 %'.

RLF
Russell Fields replied on 29-Aug-07 02:03 PM
Leslie,

Well, you have stirred up some interest.  One issue that TheSQLGuru raised
was the ANSI_PADDING setting when your table and columns were created.

But I also tried this little script:

create table #george (id int identity, num nvarchar(10))
go
insert into #george (num) values ('1234')
insert into #george (num) values ('1234 ')
go
-- Returns IDs 1 and 2
select * from #george where num = '1234'
-- Returns ID 2
select * from #george where num like '1234 %'
go
drop table #george
go

Apparently a direct column compare ignores trailing blanks, but a LIKE can
see them, if properly specified. Unfortunately for understanding your
problem, I get the same results if I CREATE TABLE with SET ANSI_PADDING ON
or OFF.  And I get the same results whether comparing with '1234 %' or
N'1234 %'.

RLF
Gert-Jan Strik replied on 29-Aug-07 02:40 PM
Wow, some quite complicated solutions out there...

As you have noticed, SQL Server handles data comparison according to the
ANSI standard. When comparing two string values of different size, the
smaller one will be padded with spaces until they are equal in size.
Then they are compared.

So the query "WHERE ID = '3020'" will also include the rows where ID =
'3020 ', and the query "WHERE ID = '3020 '" will also include rows where
ID = '3020'.

However, LIKE works differently. LIKE will not add spaces before
comparing. So the query "WHERE ID LIKE '3020 '" will not return rows
where ID = '3020', but only the exact match.

So for your situation, IMO this is by far the easiest solution. Please
note, that this will only work properly if your literal (or variable)
does not contain wildcards such as "%", "_", "[", etc. So the short
answer is:

select SOM_SalesORderID, *
from SalesOrder
where SOM_SalesORderID like '3020 '

--
Gert-Jan
Lesli replied on 29-Aug-07 05:50 PM
Hi Roy,

Your answer of all the responses was the only one which worked!  Thank you
for the syntax and Thank you to everyone else for their advice! :O)

Leslie
Lesli replied on 29-Aug-07 05:52 PM
Gert,

Thank you!  Yours was the easiest solution of all.  I read the on-line help
and saw that "like" was the only qualifier which might work, but I kept
putting the % wildcard in there and that nullified the query!

THANK YOU for your wise and easy help!

Leslie