SQL Server Filter records with parameters using like statement

fhg3lkii 于 2023-06-21 发布在其他

关注(0)|答案(3)|浏览(69)

I have a query to filter location by different regions based on postal code of that. To distinguish these regions, people must rely on the first 2 numbers of that and each region contains many different numbers (e.g: region A (1 to 25) and region B (26 to 30)).

So when I filter region A, the statement goes like this

SELECT * 
FROM Address 
WHERE PostalCode LIKE '01%' 
   OR PostalCode LIKE '02%' 
   OR PostalCode LIKE '03%' 
   OR PostalCode LIKE '01%' 
   OR PostalCode LIKE '04%' 
   OR PostalCode LIKE '05%' 
   -- all the way to:
   OR PostalCode LIKE '25%'

That would be very long and unscientific at all. I tried to find another way like using IN statement. But it didn't work.

I need an ideal to process this issue.

sql-server

来源：https://stackoverflow.com/questions/76486989/filter-records-with-parameters-using-like-statement

3条答案

按热度按时间

sshcrbum1#

For Region A:

SELECT *
FROM tbl_
WHERE Left(RegionCode, 2) BETWEEN '01' AND '25' and Region = 'A'

For Region B:

SELECT *
FROM tbl_
WHERE Left(RegionCode, 2) BETWEEN '26' AND '30' and Region = 'B'

赞(0）回复(0）举报 2023-06-21

goucqfw62#

It depends on your RDMS but you can simply get the first two chars from your postal code and perform string comparison:

SELECT *
FROM [my_table]
WHERE LEFT(PostalCode, 2) <= '25'

I guess it will be better in terms of readability to cast to numeric and work with numbers.

And as alternative, in terms of performance it will be better to create a new column, populated the region code and create an index on it.

赞(0）回复(0）举报 2023-06-21

z4bn682m3#

If it's SQL Server, you could probably use

SELECT * 
FROM Address 
WHERE PostalCode LIKE N'[01-99]%'

This would check every postalcode that start with 01 to 99.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Filter records with parameters using like statement

3条答案

相关问题

热门标签

最新问答