Unexpected query result using like

[malaygal]

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.

 

[bborissov]

I have no problems:

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

 

[malaygal]

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.

 

[gmmastros]

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....

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

 

[malaygal]

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.

 

[Olaf Doschke]

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.

 

[malaygal]

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.

 

[johnherman]

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

 

[Olaf Doschke]

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.

 

[gmmastros]

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:

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...

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

 

[malaygal]

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.

 

[Olaf Doschke]

Have you even tried this?

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.