Showing posts with label abc. Show all posts
Showing posts with label abc. Show all posts

Wednesday, March 7, 2012

How to compare strings for equality in Transact SQL

I just learned that if a VARCHAR column contains 'ABC ' (three letters
and a space) then
SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
will return the record since SQL Server incorrectly pads the shorter
value with spaces to the length of the longer before comparing them. Is
there a way to compare strings for equality that works correctly?
.Bill.trim?
SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||THECOLUMN like 'ABC'
or
THECOLUMN+'$' = 'ABC'+'$'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||In SQL Server use RTRIM
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
> I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||Grant wrote:

> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
I must be missing something. Using trim gets the same incorrect result
as not using trim. The values 'ABC ' and 'ABC' are NOT equal. If I have
a table with a VARCHAR column that contains two rows with the values
'ABC ' and 'ABC', how do I write a SELECT statement that will return
the row that contains 'ABC' but not the row that contains 'ABC '?
.Bill.|||I think this is the problem he is having:
if 'ABC ' = 'ABC' print 'True' else print 'False'
Result: True
I am assuming he wants this condition to be False. In that base, he actually
doesn't want to trim the spaces.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to be
*different* (in most configurations, SQL Server considers them equal because
the trailing spaces on varchar columns are ignored).
So, using RTRIM() does not help because it yields the same result the OP is
already getting.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||T-SQL has RTRIM() and LTRIM() but no TRIM().
For other info, please see my reply to Jack.
"Grant" <email@.nowhere.com> wrote in message
news:ejQpurxMGHA.3144@.TK2MSFTNGP11.phx.gbl...
> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'|||Aaron Bertrand [SQL Server MVP] wrote:

> Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to
> be different
That is exactly what I want.
.Bill.|||> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them.
Actually, SQL Server is ignoring the trailing spaces, but the result is the
same.
There are a few ways to solve this, JT's solution is pretty simple to
implement...

how to compare rows from oldtbl to curtbl using Like?

oldtbl contains these CoNames
'abc'
'def'
'ghi'
'jkl'
curtbl contains these CoNames
'abc inc'
'def inc'
'jkl inc'
Note that curtbl does not contain 'ghi inc'
I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
select Coname from oldtbl where CoName Like (select CoName from curTbl)
select t1.CoName from oldtbl t1 join curtbl t2 On
t1.CoName Like t2.CoName --this did not return anthing
Is there a way to do this using Like? If yes, what is the correct syntax?
If not, is there another way to compare these rows?
Thanks,
RichI had my tables backwards. I need to retrieve rows from curtbl where CoName
is like oldtbl.CoName. I tried this syntax which actually did return rows,
but is quite slow.
select t1.CoName from curtbl t1 join oldtbl t2 on
t1.CoName like '%'+t2.CoName+'%'
My other alternative is to loop through oldtbl (all distinct CoNames) using
a cursor. I am just checking if there is a better way than a cursor.
"Rich" wrote:

> oldtbl contains these CoNames
> 'abc'
> 'def'
> 'ghi'
> 'jkl'
> curtbl contains these CoNames
> 'abc inc'
> 'def inc'
> 'jkl inc'
> Note that curtbl does not contain 'ghi inc'
> I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
> select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
> select Coname from oldtbl where CoName Like (select CoName from curTbl)
> select t1.CoName from oldtbl t1 join curtbl t2 On
> t1.CoName Like t2.CoName --this did not return anthing
> Is there a way to do this using Like? If yes, what is the correct syntax?
> If not, is there another way to compare these rows?
> Thanks,
> Rich|||use this.. and let me know if it works
select a.CoName from oldTbl a
where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName +
'%')
"Rich" wrote:

> oldtbl contains these CoNames
> 'abc'
> 'def'
> 'ghi'
> 'jkl'
> curtbl contains these CoNames
> 'abc inc'
> 'def inc'
> 'jkl inc'
> Note that curtbl does not contain 'ghi inc'
> I need to retrieve rows from oldtbl where CoName Like CoName in curtbl
> select CoName from oldTbl where CoName like '%' + curtbl.CoName + '%'
> select Coname from oldtbl where CoName Like (select CoName from curTbl)
> select t1.CoName from oldtbl t1 join curtbl t2 On
> t1.CoName Like t2.CoName --this did not return anthing
> Is there a way to do this using Like? If yes, what is the correct syntax?
> If not, is there another way to compare these rows?
> Thanks,
> Rich|||then try this
select t1.CoName from curtbl t1 join oldtbl t2 on
charindex('%'+t2.CoName+'%',t1.CoName) >0
"Rich" wrote:
> I had my tables backwards. I need to retrieve rows from curtbl where CoNa
me
> is like oldtbl.CoName. I tried this syntax which actually did return rows
,
> but is quite slow.
> select t1.CoName from curtbl t1 join oldtbl t2 on
> t1.CoName like '%'+t2.CoName+'%'
> My other alternative is to loop through oldtbl (all distinct CoNames) usin
g
> a cursor. I am just checking if there is a better way than a cursor.
>
>
> "Rich" wrote:
>|||Hi Omnibuzz, its me again :)
Thank you for your reply. I tried your method. It works, but wasn't any
faster than the one that I tried. But I believe your methos is probably mor
e
accurate. I did try a cursor, but I am still having a problem setting up
delimeters. I am still not clear on the rule for delimiting things in
Dynamic Sql. The one thing about the cursor is that I can print out a count
to let me know the progress of the cursor. Here is the cursor - which has a
syntax error in the sql statement. Any suggestions appreciated how to fix
this. Note: I am using the actual col names and table names here.
Declare @.coid varchar(50), @.i int
DECLARE curcoid CURSOR FOR
SELECT CoID FROM #temp3
set @.i = 1
OPEN curcoid
FETCH NEXT FROM curcoid into @.coid
WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.i
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like '%'''' + @.coid + ''''%'')
set @.i = @.i + 1
FETCH NEXT FROM curcoid into @.coid
END
CLOSE curcoid
DEALLOCATE curcoid
GO
Thanks,
Rich
"Omnibuzz" wrote:
> use this.. and let me know if it works
> select a.CoName from oldTbl a
> where exists ( select 1 from curtbl b where b.CoName like '%' + a.CoName
+
> '%')
>
> --
>
>
> "Rich" wrote:
>|||Hi Rich..
I would say its better to use a query than this approach. try to index on
that column and see.
your approach is a double killer. a cursor and dynamic sql.. whooooh.. lets
keep the cursor out for this problem.
Hope this helps.
--
"Rich" wrote:
> Hi Omnibuzz, its me again :)
> Thank you for your reply. I tried your method. It works, but wasn't any
> faster than the one that I tried. But I believe your methos is probably m
ore
> accurate. I did try a cursor, but I am still having a problem setting up
> delimeters. I am still not clear on the rule for delimiting things in
> Dynamic Sql. The one thing about the cursor is that I can print out a cou
nt
> to let me know the progress of the cursor. Here is the cursor - which has
a
> syntax error in the sql statement. Any suggestions appreciated how to fix
> this. Note: I am using the actual col names and table names here.
>
> Declare @.coid varchar(50), @.i int
> DECLARE curcoid CURSOR FOR
> SELECT CoID FROM #temp3
> set @.i = 1
> OPEN curcoid
> FETCH NEXT FROM curcoid into @.coid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print @.i
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like '%'''' + @.coid + ''''%'')
> set @.i = @.i + 1
> FETCH NEXT FROM curcoid into @.coid
> END
> CLOSE curcoid
> DEALLOCATE curcoid
> GO
> Thanks,
> Rich
> "Omnibuzz" wrote:
>|||Well I am currently running
select t1.CoName from curtbl t1 join oldtbl t2 on
charindex('%'+t2.CoName+'%',t1.CoName) >0
as we speak (as I type). On the cursor, I can limit my old table to like 10
CoNames. So I iterate only 10 times in the cursor. My thing is that I was
trying out the cursor just for exercise - mostly on writing dynamic sql. Ma
y
I request if you could correct my syntax on the sql statement?
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like '%'''' + @.coid + ''''%'')
I fetch the CoName into @.coid. I am trying to simulate
Insert Into #temp4 select recordID, coid from subscriber where coid like
'%abc%'
How do I delimit @.coid above ?
Thanks again for your help
Rich
"Omnibuzz" wrote:
> Hi Rich..
> I would say its better to use a query than this approach. try to index
on
> that column and see.
> your approach is a double killer. a cursor and dynamic sql.. whooooh.. let
s
> keep the cursor out for this problem.
> Hope this helps.
> --
>
>
> "Rich" wrote:
>|||use this
exec('Insert Into #temp4 select recordID, CoID from subscriber where
CoID Like ''%''' + @.coid + '''%''')
Hope this helps.
--
"Rich" wrote:
> Well I am currently running
> select t1.CoName from curtbl t1 join oldtbl t2 on
> charindex('%'+t2.CoName+'%',t1.CoName) >0
> as we speak (as I type). On the cursor, I can limit my old table to like
10
> CoNames. So I iterate only 10 times in the cursor. My thing is that I wa
s
> trying out the cursor just for exercise - mostly on writing dynamic sql.
May
> I request if you could correct my syntax on the sql statement?
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like '%'''' + @.coid + ''''%'')
> I fetch the CoName into @.coid. I am trying to simulate
> Insert Into #temp4 select recordID, coid from subscriber where coid like
> '%abc%'
> How do I delimit @.coid above ?
> Thanks again for your help
> Rich
>
> "Omnibuzz" wrote:
>|||Thank you. Well, the cursor ran this time, but I got an error message for
each of 10 iterations in the cursor. Note: I modified the cursor to only
select (and not insert - for if there is no match don't insert a null row.
Here is my modified cursor and the error message - using only the select
statment. Could it be that since none of the 10 CoNames I am passing in hav
e
a match in the current table ?
----
Declare @.coid varchar(50)
DECLARE curcoid CURSOR FOR
SELECT CoID FROM #temp5
OPEN curcoid
FETCH NEXT FROM curcoid into @.coid
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid +
'''%''')
FETCH NEXT FROM curcoid into @.coid
END
CLOSE curcoid
DEALLOCATE curcoid
GO
----
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'NEW'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TRENWICK'.
Server: Msg 170, Level 15, State 1, Line 1
...
Near 'New', Near 'Trenwick' these are part of the CoNames I am comparing
against:
'NEW CENTURY ENERGIES INC-CO'
'TRENWICK AMERICA CORP'
...
exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid +
'''%''')
where coid like '%NEW CENTURY ENERGIES INC-CO%' -- no match in my actual tbl
"Omnibuzz" wrote:
> use this
> exec('Insert Into #temp4 select recordID, CoID from subscriber where
> CoID Like ''%''' + @.coid + '''%''')
> Hope this helps.
> --
>
>
> "Rich" wrote:
>|||Well, I modified the delimiters as follows which ended up working in the cur
sor
exec('select recordID, CoID from subscriber where CoID Like ''%' + @.coid +
'%''')
And when I used a smaller number of CoNames, your method
select a.coid from subscriber a
where exists ( select 1 from #temp6 b where b.coid like '%' + a.coid + '%')
worked very well. I really appreciate all of your help. Thank you very
much.
Rich
"Rich" wrote:
> Thank you. Well, the cursor ran this time, but I got an error message for
> each of 10 iterations in the cursor. Note: I modified the cursor to only
> select (and not insert - for if there is no match don't insert a null row.
> Here is my modified cursor and the error message - using only the select
> statment. Could it be that since none of the 10 CoNames I am passing in h
ave
> a match in the current table ?
> ----
--
> Declare @.coid varchar(50)
> DECLARE curcoid CURSOR FOR
> SELECT CoID FROM #temp5
> OPEN curcoid
> FETCH NEXT FROM curcoid into @.coid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid
+
> '''%''')
> FETCH NEXT FROM curcoid into @.coid
> END
> CLOSE curcoid
> DEALLOCATE curcoid
> GO
> ----
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'NEW'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'TRENWICK'.
> Server: Msg 170, Level 15, State 1, Line 1
> ...
> Near 'New', Near 'Trenwick' these are part of the CoNames I am comparing
> against:
>
> 'NEW CENTURY ENERGIES INC-CO'
> 'TRENWICK AMERICA CORP'
> ...
> exec('select recordID, CoID from subscriber where CoID Like ''%''' + @.coid
+
> '''%''')
> where coid like '%NEW CENTURY ENERGIES INC-CO%' -- no match in my actual t
bl
>
>
> "Omnibuzz" wrote:
>