INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Unexpected query result using like

Unexpected query result using like

(OP)
My table has a varchar column that contains 5-digit numeric values.
When I use the like query to filter, I am not getting the expected resultss.

ZipCode

20019
10027
00199
10019
...
...

This is my condition where zipcode like '%0019%'
Condition only returns

20019
10019

but not 00199

Any help will be greatly appreciated.

RE: Unexpected query result using like

I have no problems:

CODE

DECLARE @Zip TABLE (Pkod varchar(10))
INSERT INTO @Zip VALUES ('20019')
INSERT INTO @Zip VALUES ('10027')
INSERT INTO @Zip VALUES ('00199')
INSERT INTO @Zip VALUES ('10019')

SELECT * FROM @Zip WHERE Pkod LIKE '%0019%' 

Borislav Borissov
VFP9 SP2, SQL Server

RE: Unexpected query result using like

(OP)
My apologies, the column type is (decimal,5).
My program cast it to string as so:

strFilter = " AND CONVERT(zipcode,'System.String') LIKE '%" & strPart & "%'"

where strPart = "0019" (user input).

Went to sql server and directly queried the table

where zipcode like '%0019%'

and the same result.



RE: Unexpected query result using like

I have a lot of experience in this area. Zip Codes should be stored as a string. In the long run, changing this to string (varchar) will make things a lot easier.

When you convert a decimal to a string, there are no leading zeros unless you put them there.

using Boris's example from above....

CODE

DECLARE @Zip TABLE (ZipCode decimal(5,0))
INSERT INTO @Zip VALUES ('20019')
INSERT INTO @Zip VALUES ('10027')
INSERT INTO @Zip VALUES ('00199')
INSERT INTO @Zip VALUES ('10019')

SELECT * 
FROM   @Zip 
WHERE  Right('00000' + convert(varchar(5), ZipCode), 5) LIKE '%0019%' 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Unexpected query result using like

(OP)
I agree with you on the zipcode, unfortunately I have to work with these existing tables.

WHERE Right('00000' + convert(varchar(5), ZipCode), 5) LIKE '%0019%' worked great in sql server.

But I have to use this string as a binding datasource filter and my program raised an error on the Right function.

Any idea how I can convert this for use as a filter?

Thanks.

RE: Unexpected query result using like

Well, you have to apply whatever converts numerc to a string with leading zeros in your frontend language. That's a question for anyother forum.
The expression will work for MSSQL and I wonder why it wouldn't work with any frontend making that query towards MSSQL, the result has no WHERE caluse attached, that needs expressions working in the frontend language, the result simply contains the ZIP 19, which should display as 00019 anyway, so tehre must be some frontend expression or logic making that display converision.

Bye, Olaf.

RE: Unexpected query result using like

(OP)
That's what I have been struggling for hours for. Front-end is VB.net
So far I have this working:

strFilter = " AND CONVERT(zipcode, 'System.String') LIKE '%" & txtFilter.Text & "%'"

Need to somehow pad this CONVERT(zipcode, 'System.String') with zeroes.

Tried tostring and format, but binding datasource filter don't recognize these functions.

RE: Unexpected query result using like

if you can add a column called Postal_Code char(6) or char(10) for zip+4. Populate the Postal Code column from the Zip Code column and add a trigger on insert and update to populate Postal_Code from the Zip Code column.

Voila! Existing queries using Zip Code continue to function meanwhile the new and improved column can begin to be used.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Unexpected query result using like

Makes me wonder, how that should work, what's the background? SqlCommand or Entity Framwork, SQL or Linq?

MSSQL might only understand CONVERT(zipcode,"System.String') if configured to allow .NET code within stored procs, I am not aware of MSSQL executing .NET code as adhoc queries. And also a mixture of T-SQL (CONVERT) and .NET types is questionable.

What works in Sql Server Management Studio works within your VB.NET code as SQLCommand, it's still MSSQL executing that T-SQL query, it's not .NET executing that.

Where RIGHT() wouldn't work is within a Linq query on a collection or datatable or such thing within VB.NET

Bye, Olaf.

RE: Unexpected query result using like

John Herman,

I like your approach, but instead of adding a column and using triggers to maintain the data, I would create a computed column. For those who don't know, computed columns basically allow you to have a column this is a function of other data.

For example, you can have this:

CODE

use tempdb

Create Table Circles(Radius Decimal(10,2), Color VarChar(20))

Insert Into Circles Values(1, 'Red');
Insert Into Circles Values(2, 'Blue');
Insert Into Circles Values(3, 'Green');

Select * From Circles

Alter Table Circles Add Circumference As Radius * 2 * PI()
Alter Table Circles Add Area As Radius * Radius * PI()

Select * From Circles

Drop Table Circles 

So...

CODE

Alter 
Table [YourTableNameHere] 
Add   PostalCode As Right('00000' + convert(varchar(5), ZipCode), 5) 

Now you have a postal code column that is a varchar and can be used from your front end without having to jump through hoops.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Unexpected query result using like

(OP)
Thanks for all the replies and suggestions.
I only have a read access on the db, so I can only manipulate the data that the application receive in the form of a binding data source.

RE: Unexpected query result using like

Have you even tried this?

CODE

Private bindingSource1 As New BindingSource()
bindingSource1.DataSource = GetData("SELECT Right('00000' + convert(varchar(5), ZipCode), 5) as zipcode FROM Yourtable WHERE Right('00000' + convert(varchar(5), ZipCode), 5) LIKE '%0019%'") 

And then setting a DataGridview.DataSource to that BindingSource.

I'm 99% sure you're on the totally wrong track trying CONVERT(zipcode, 'System.String'), you're querying SQL Server with T-SQL, the Query is merely a string to .NET, executed in MSSQL in it's query langauge T-SQL, not VB.NET.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close