How to use wild cards with numbers

Get Help SQL
,
#1

I am trying to get a list of customers based on zip code of GA. I am not getting any answers

SELECT *

FROM transaction_data

WHERE zip BETWEEN 300% AND 319%;

Please advise how

#2

Did you try surrounding the wildcard in single quotes?
ex: '300%' AND '319%'

https://learnsql.com/blog/sql-wildcard/

#3

You can copy the zip INTEGER to another column of type VARCHAR and then query:

ALTER TABLE transaction_data
ADD COLUMN zip_code VARCHAR;

UPDATE transaction_data
SET zip_code = zip;

SELECT full_name, zip_code FROM transaction_data
WHERE zip_code BETWEEN '300%' AND '319%' OR zip_code LIKE '398%';

That returns correct answer for me.

1 Like
#4

No I have not tried that

#5

@lisalisaj

For project RFP Fraud Detection Challenge 7 it states the following:

– 7
– Challenge
– Return only those customers residing in GA. Use the list of ZIP CODE prefixes
– (List of ZIP Code prefixes - Wikipedia)
– to determine the best query for zip codes belonging to Georgia(GA).

PREFIXES for GA zips are 300-319

My query is the following,

SELECT * FROM transaction_data
WHERE zip LIKE ‘30%’ AND ‘31%’;

I get a return for LIKE ‘30%’ however, when I try to join the two with AND I cant get a return. I’ve also tried BETWEEN and no results.

#6

You want a range of values between certain parameters. If you scroll up here and view tera’s response you will get more of an idea.
The way that you’re using the LIKE clause here only refers to that first value.

Also you might want to use BETWEEN here. See: https://www.zentut.com/sql-tutorial/sql-between/

#7

After reading and applying Tera’s line, it worked. However, I do not understand why you would want to add zip_code column and have it = zip when there is already a column (zip) with the zip codes? Additionally, why does Tera have ‘398%’ at the end of your line when the requirement is to retrieve zips from 300-319?

#8

Got it in a different way, wasnt able to make it work with case ```BETWEEN ‘300%’ AND ‘319%’


SELECT *,
  CASE
  WHEN zip between 30000 AND 31999 THEN "GA"
  WHEN zip between 39800 AND 39899 THEN "GA"
  ELSE "Other"
  END AS Zip1
FROM transaction_data
where zip1 = 'GA'
order by zip asc
1 Like
#9

BETWEEN did not work for me either.

Here’s my query:

SELECT *
FROM transaction_data
WHERE zip LIKE '30%' OR zip LIKE '31%' OR zip LIKE '398%' OR zip LIKE '399%';

I still think there is a lot of OR, but it seems to be working fine.

1 Like
#10

Yes, thanks this worked.