RE: sql LIKE question

2004-12-07 Thread Jerry Barnes 
Are all of the account numbers the same length?  


J 

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 06, 2004 6:11 PM
To: CF-Talk
Subject: sql LIKE question

Is there any way to force LIKE to match on leading zeros?  If I have a
query along the lines of:
 
select accountkey
from companies
where accountno LIKE '%#searchString#%'
 
and I search with a string of: 0059
 
I am getting results like:
 
05976574
12343459
59748311
 
It treats it as if I entered 59 in the search string.
 
Thanks,
Scott

---
[This E-mail scanned for viruses by Declude Virus]




~|
Special thanks to the CF Community Suite Silver Sponsor - RUWebby
http://www.ruwebby.com

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186434
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: sql LIKE question

2004-12-07 Thread Pascal Peters 
Are you sure searchString contains 0059 and not 59 ?? How is it
defined?

Pascal

 -Original Message-
 From: Scott Mulholland [mailto:[EMAIL PROTECTED]
 Sent: 07 December 2004 00:11
 To: CF-Talk
 Subject: sql LIKE question
 
 Is there any way to force LIKE to match on leading zeros?  If I have a
 query along the lines of:
 
 select accountkey
 from companies
 where accountno LIKE '%#searchString#%'
 
 and I search with a string of: 0059
 
 I am getting results like:
 
 05976574
 12343459
 59748311
 
 It treats it as if I entered 59 in the search string.
 
 Thanks,
 Scott
 
 ---
 [This E-mail scanned for viruses by Declude Virus]
 
 
 

~|
Special thanks to the CF Community Suite Silver Sponsor - RUWebby
http://www.ruwebby.com

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186438
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: sql LIKE question

2004-12-07 Thread Scott Mulholland 
No, they vary between 8-10 characters, the field is a varchar(10), sql
server.

-Original Message-
From: Jerry Barnes [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 07, 2004 8:27 AM
To: CF-Talk
Subject: RE: sql LIKE question


Are all of the account numbers the same length?  


J 

-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 06, 2004 6:11 PM
To: CF-Talk
Subject: sql LIKE question

Is there any way to force LIKE to match on leading zeros?  If I have a
query along the lines of:
 
select accountkey
from companies
where accountno LIKE '%#searchString#%'
 
and I search with a string of: 0059
 
I am getting results like:
 
05976574
12343459
59748311
 
It treats it as if I entered 59 in the search string.
 
Thanks,
Scott

---
[This E-mail scanned for viruses by Declude Virus]






~|
Special thanks to the CF Community Suite Silver Sponsor - New Atlanta
http://www.newatlanta.com

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186439
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: sql LIKE question

2004-12-07 Thread Scott Mulholland 
Yeah, looking at the query in the debugging information it is shown as
0059.

-Original Message-
From: Pascal Peters [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, December 07, 2004 9:12 AM
To: CF-Talk
Subject: RE: sql LIKE question


Are you sure searchString contains 0059 and not 59 ?? How is it
defined?

Pascal

 -Original Message-
 From: Scott Mulholland [mailto:[EMAIL PROTECTED]
 Sent: 07 December 2004 00:11
 To: CF-Talk
 Subject: sql LIKE question
 
 Is there any way to force LIKE to match on leading zeros?  If I have a

 query along the lines of:
 
 select accountkey
 from companies
 where accountno LIKE '%#searchString#%'
 
 and I search with a string of: 0059
 
 I am getting results like:
 
 05976574
 12343459
 59748311
 
 It treats it as if I entered 59 in the search string.
 
 Thanks,
 Scott
 
 ---
 [This E-mail scanned for viruses by Declude Virus]
 
 
 



~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186471
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: sql LIKE question

2004-12-07 Thread Jerry Barnes 
I am not a SQL guru so this may be way off.

select  accountkey
fromcompanies
where   left(accountno,6) = '00'
And accountno LIKE '%#searchString#%' 

You would have to count the leading zeros everytime though and adjust
accordingly 
Which wouldn't be much fun.





-Original Message-
From: Scott Mulholland [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 06, 2004 6:11 PM
To: CF-Talk
Subject: sql LIKE question

Is there any way to force LIKE to match on leading zeros?  If I have a
query along the lines of:
 
select accountkey
from companies
where accountno LIKE '%#searchString#%'
 
and I search with a string of: 0059
 
I am getting results like:
 
05976574
12343459
59748311
 
It treats it as if I entered 59 in the search string.
 
Thanks,
Scott

---
[This E-mail scanned for viruses by Declude Virus]




~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186512
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: sql LIKE question

2004-12-06 Thread Charles Heizer 
What is the column type in the database, and what kind of database is  
it?

- Charles


On Dec 6, 2004, at 3:10 PM, Scott Mulholland wrote:

 Is there any way to force LIKE to match on leading zeros?  If I have a
 query along the lines of:

 select accountkey
 from companies
 where accountno LIKE '%#searchString#%'

 and I search with a string of: 0059

 I am getting results like:

 05976574
 12343459
 59748311

 It treats it as if I entered 59 in the search string.

 Thanks,
 Scott

 ---
 [This E-mail scanned for viruses by Declude Virus]


 

~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186396
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54