Postcode range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Postcode range

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-03-11 : 07:54:44

Hi

I have a postcode column in a table. I want to run a SQL report to return all customers informaton that these postcodes have included in them.

For example

Select * from people
Where Postcode like 'W10%'

but how can I run this so that it applies for multiple post codes in the same query? The postcodes I need are

AL1,AL10,AL2,AL3,AL4,AL5,AL6,AL7,AL8,AL9,BR1,BR2,BR3,BR4,BR5,BR6,BR7,BR8,CB10,CB11,CM1,CM11,CM12,CM13,CM14,CM15,CM16,CM17,CM18,CM19,CM2,CM20,CM21,CM22,CM23,CM24,CM4,CM5,CM6,CM99,CR0,CR2,CR3,CR4,CR5,CR6,CR7,CR8,CR9,DA1,DA10,DA11,DA12,DA13,DA14,DA15,DA16,DA17,DA18,DA2,DA3,DA4,DA5,DA6,DA7,DA8,DA9,E1,E10,E11,E12,E13,E14,E15,E16,E17,E18,E1W,E2,E3,E4,E5,E6,E7,E77,E8,E9,E98,EC1,EC1A,EC1M,EC1N,EC1P,EC1R,EC1V,EC1Y,EC2,EC2A,EC2M,EC2N,EC2P,EC2R,EC2V,EC2Y,EC3,EC3A,EC3M,EC3N,EC3P,EC3R,EC3V,EC4,EC4A,EC4M,EC4N,EC4P,EC4R,EC4V,EC4Y,EC50,EN1,EN10,EN11,EN2,EN3,EN4,EN5,EN6,EN7,EN8,EN9,GU20,GU21,GU23,GU25,HA0,HA1,HA2,HA3,HA4,HA5,HA6,HA7,HA8,HA9,HP1,HP10,HP11,HP12,HP13,HP14,HP15,HP16,HP17,HP19,HP2,HP20,HP21,HP22,HP23,HP27,HP3,HP4,HP5,HP6,HP7,HP8,HP9,IG1,IG10,IG11,IG2,IG3,IG4,IG5,IG6,IG7,IG8,IG9,KT1,KT10,KT11,KT12,KT13,KT14,KT15,KT16,KT17,KT18,KT19,KT2,KT20,KT21,KT22,KT23,KT3,KT4,KT5,KT6,KT7,KT8,KT9,LU1,LU2,LU3,LU4,LU5,LU6,LU7,MK45,N1,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19,N1P,N2,N20,N21,N22,N3,N4,N5,N6,N7,N8,N9,NW1,NW10,NW11,NW1W,NW2,NW3,NW4,NW5,NW6,NW7,NW8,NW9,RG42,RH8,RM1,RM10,RM11,RM12,RM13,RM14,RM15,RM16,RM17,RM18,RM19,RM2,RM20,RM3,RM4,RM5,RM6,RM7,RM8,RM9,SE1,SE10,SE11,SE12,SE13,SE14,SE15,SE16,SE17,SE18,SE19,SE1P,SE2,SE20,SE21,SE22,SE23,SE24,SE25,SE26,SE27,SE28,SE3,SE4,SE5,SE6,SE7,SE8,SE9,SG1,SG10,SG11,SG12,SG13,SG14,SG15,SG16,SG17,SG18,SG2,SG3,SG4,SG5,SG6,SG7,SG8,SG9,SL0,SL1,SL2,SL3,SL4,SL5,SL6,SL7,SL8,SL9,SL95,SM1,SM2,SM3,SM4,SM5,SM6,SM7,SS11,SS12,SS13,SS14,SS15,SS16,SS17,SW1,SW10,SW11,SW12,SW13,SW14,SW15,SW16,SW17,SW18,SW19,SW1A,SW1E,SW1H,SW1P,SW1V,SW1W,SW1X,SW1Y,SW2,SW20,SW3,SW4,SW5,SW6,SW7,SW8,SW9,SW99,TN14,TN16,TW1,TW10,TW11,TW12,TW13,TW14,TW15,TW16,TW17,TW18,TW19,TW2,TW20,TW3,TW4,TW5,TW6,TW7,TW8,TW9,UB1,UB10,UB11,UB18,UB2,UB3,UB4,UB5,UB6,UB7,UB8,UB9,W1,W10,W11,W12,W13,W14,W1A,W1B,W1C,W1D,W1F,W1G,W1H,W1J,W1K,W1M,W1S,W1T,W1U,W1W,W2,W3,W4,W5,W6,W7,W8,W9,WC1,WC1A,WC1B,WC1E,WC1H,WC1N,WC1R,WC1V,WC1X,WC2,WC2A,WC2B,WC2E,WC2H,WC2N,WC2R,WD1,WD17,WD18,WD19,WD2,WD23,WD24,WD25,WD3,WD4,WD5,WD6,WD7

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-11 : 08:08:46

Split the comma-separated list into a virtual table and join to that table. There are string splitters available on line - e.g. http://www.sqlservercentral.com/articles/Tally+Table/72993/ If you install the function from that article, then the query would be like this.

DECLARE @commaseparatedList VARCHAR(8000) 
	= 'AL1,AL10,AL2,AL3,AL4,AL5,AL6,AL7,AL8,AL9,BR1';
SELECT  p.*
FROM    people p
        INNER JOIN dbo.delimitedsplit8k(@commaseparatedList, ',') s 
			ON p.postcode LIKE s.item + '%';

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-03-11 : 08:40:47

Hi

Ok I have the data but how can I remove duplicate records. Within the results I have multiple records for the same customer in CustomerID.

When I try to use Group by CustomerID I get this error

Msg 8120, Level 16, State 1, Line 1
Column 'Address.AddressID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

here is the query

Select * from Address
WHERE Zip Like 'AL1%'
OR Zip Like 'AL10%'
OR Zip Like 'AL2%'
OR Zip Like 'AL3%'
OR Zip Like 'AL4%'
OR Zip Like 'AL5%'
OR Zip Like 'AL6%'
OR Zip Like 'AL7%'
OR Zip Like 'AL8%'
OR Zip Like 'AL9%'
OR Zip Like 'BR1%'
OR Zip Like 'BR2%'
OR Zip Like 'BR3%'
OR Zip Like 'BR4%'
OR Zip Like 'BR5%'
OR Zip Like 'BR6%'
OR Zip Like 'BR7%'
OR Zip Like 'BR8%'
OR Zip Like 'CB10%'
OR Zip Like 'CB11%'
OR Zip Like 'CM1%'
OR Zip Like 'CM11%'
OR Zip Like 'CM12%'
OR Zip Like 'CM13%'
OR Zip Like 'CM14%'
OR Zip Like 'CM15%'
OR Zip Like 'CM16%'
OR Zip Like 'CM17%'
OR Zip Like 'CM18%'
OR Zip Like 'CM19%'
OR Zip Like 'CM2%'
OR Zip Like 'CM20%'
OR Zip Like 'CM21%'
OR Zip Like 'CM22%'
OR Zip Like 'CM23%'
OR Zip Like 'CM24%'
OR Zip Like 'CM4%'
OR Zip Like 'CM5%'
OR Zip Like 'CM6%'
OR Zip Like 'CM99%'
OR Zip Like 'CR0%'
OR Zip Like 'CR2%'
OR Zip Like 'CR3%'
OR Zip Like 'CR4%'
OR Zip Like 'CR5%'
OR Zip Like 'CR6%'
OR Zip Like 'CR7%'
OR Zip Like 'CR8%'
OR Zip Like 'CR9%'
OR Zip Like 'DA1%'
OR Zip Like 'DA10%'
OR Zip Like 'DA11%'
OR Zip Like 'DA12%'
OR Zip Like 'DA13%'
OR Zip Like 'DA14%'
OR Zip Like 'DA15%'
OR Zip Like 'DA16%'
OR Zip Like 'DA17%'
OR Zip Like 'DA18%'
OR Zip Like 'DA2%'
OR Zip Like 'DA3%'
OR Zip Like 'DA4%'
OR Zip Like 'DA5%'
OR Zip Like 'DA6%'
OR Zip Like 'DA7%'
OR Zip Like 'DA8%'
OR Zip Like 'DA9%'
OR Zip Like 'E1%'
OR Zip Like 'E10%'
OR Zip Like 'E11%'
OR Zip Like 'E12%'
OR Zip Like 'E13%'
OR Zip Like 'E14%'
OR Zip Like 'E15%'
OR Zip Like 'E16%'
OR Zip Like 'E17%'
OR Zip Like 'E18%'
OR Zip Like 'E1W%'
OR Zip Like 'E2%'
OR Zip Like 'E3%'
OR Zip Like 'E4%'
OR Zip Like 'E5%'
OR Zip Like 'E6%'
OR Zip Like 'E7%'
OR Zip Like 'E77%'
OR Zip Like 'E8%'
OR Zip Like 'E9%'
OR Zip Like 'E98%'
OR Zip Like 'EC1%'
OR Zip Like 'EC1A%'
OR Zip Like 'EC1M%'
OR Zip Like 'EC1N%'
OR Zip Like 'EC1P%'
OR Zip Like 'EC1R%'
OR Zip Like 'EC1V%'
OR Zip Like 'EC1Y%'
OR Zip Like 'EC2%'
OR Zip Like 'EC2A%'
OR Zip Like 'EC2M%'
OR Zip Like 'EC2N%'
OR Zip Like 'EC2P%'
OR Zip Like 'EC2R%'
OR Zip Like 'EC2V%'
OR Zip Like 'EC2Y%'
OR Zip Like 'EC3%'
OR Zip Like 'EC3A%'
OR Zip Like 'EC3M%'
OR Zip Like 'EC3N%'
OR Zip Like 'EC3P%'
OR Zip Like 'EC3R%'
OR Zip Like 'EC3V%'
OR Zip Like 'EC4%'
OR Zip Like 'EC4A%'
OR Zip Like 'EC4M%'
OR Zip Like 'EC4N%'
OR Zip Like 'EC4P%'
OR Zip Like 'EC4R%'
OR Zip Like 'EC4V%'
OR Zip Like 'EC4Y%'
OR Zip Like 'EC50%'
OR Zip Like 'EN1%'
OR Zip Like 'EN10%'
OR Zip Like 'EN11%'
OR Zip Like 'EN2%'
OR Zip Like 'EN3%'
OR Zip Like 'EN4%'
OR Zip Like 'EN5%'
OR Zip Like 'EN6%'
OR Zip Like 'EN7%'
OR Zip Like 'EN8%'
OR Zip Like 'EN9%'
OR Zip Like 'GU20%'
OR Zip Like 'GU21%'
OR Zip Like 'GU23%'
OR Zip Like 'GU25%'
OR Zip Like 'HA0%'
OR Zip Like 'HA1%'
OR Zip Like 'HA2%'
OR Zip Like 'HA3%'
OR Zip Like 'HA4%'
OR Zip Like 'HA5%'
OR Zip Like 'HA6%'
OR Zip Like 'HA7%'
OR Zip Like 'HA8%'
OR Zip Like 'HA9%'
OR Zip Like 'HP1%'
OR Zip Like 'HP10%'
OR Zip Like 'HP11%'
OR Zip Like 'HP12%'
OR Zip Like 'HP13%'
OR Zip Like 'HP14%'
OR Zip Like 'HP15%'
OR Zip Like 'HP16%'
OR Zip Like 'HP17%'
OR Zip Like 'HP19%'
OR Zip Like 'HP2%'
OR Zip Like 'HP20%'
OR Zip Like 'HP21%'
OR Zip Like 'HP22%'
OR Zip Like 'HP23%'
OR Zip Like 'HP27%'
OR Zip Like 'HP3%'
OR Zip Like 'HP4%'
OR Zip Like 'HP5%'
OR Zip Like 'HP6%'
OR Zip Like 'HP7%'
OR Zip Like 'HP8%'
OR Zip Like 'HP9%'
OR Zip Like 'IG1%'
OR Zip Like 'IG10%'
OR Zip Like 'IG11%'
OR Zip Like 'IG2%'
OR Zip Like 'IG3%'
OR Zip Like 'IG4%'
OR Zip Like 'IG5%'
OR Zip Like 'IG6%'
OR Zip Like 'IG7%'
OR Zip Like 'IG8%'
OR Zip Like 'IG9%'
OR Zip Like 'KT1%'
OR Zip Like 'KT10%'
OR Zip Like 'KT11%'
OR Zip Like 'KT12%'
OR Zip Like 'KT13%'
OR Zip Like 'KT14%'
OR Zip Like 'KT15%'
OR Zip Like 'KT16%'
OR Zip Like 'KT17%'
OR Zip Like 'KT18%'
OR Zip Like 'KT19%'
OR Zip Like 'KT2%'
OR Zip Like 'KT20%'
OR Zip Like 'KT21%'
OR Zip Like 'KT22%'
OR Zip Like 'KT23%'
OR Zip Like 'KT3%'
OR Zip Like 'KT4%'
OR Zip Like 'KT5%'
OR Zip Like 'KT6%'
OR Zip Like 'KT7%'
OR Zip Like 'KT8%'
OR Zip Like 'KT9%'
OR Zip Like 'LU1%'
OR Zip Like 'LU2%'
OR Zip Like 'LU3%'
OR Zip Like 'LU4%'
OR Zip Like 'LU5%'
OR Zip Like 'LU6%'
OR Zip Like 'LU7%'
OR Zip Like 'MK45%'
OR Zip Like 'N1%'
OR Zip Like 'N10%'
OR Zip Like 'N11%'
OR Zip Like 'N12%'
OR Zip Like 'N13%'
OR Zip Like 'N14%'
OR Zip Like 'N15%'
OR Zip Like 'N16%'
OR Zip Like 'N17%'
OR Zip Like 'N18%'
OR Zip Like 'N19%'
OR Zip Like 'N1P%'
OR Zip Like 'N2%'
OR Zip Like 'N20%'
OR Zip Like 'N21%'
OR Zip Like 'N22%'
OR Zip Like 'N3%'
OR Zip Like 'N4%'
OR Zip Like 'N5%'
OR Zip Like 'N6%'
OR Zip Like 'N7%'
OR Zip Like 'N8%'
OR Zip Like 'N9%'
OR Zip Like 'NW1%'
OR Zip Like 'NW10%'
OR Zip Like 'NW11%'
OR Zip Like 'NW1W%'
OR Zip Like 'NW2%'
OR Zip Like 'NW3%'
OR Zip Like 'NW4%'
OR Zip Like 'NW5%'
OR Zip Like 'NW6%'
OR Zip Like 'NW7%'
OR Zip Like 'NW8%'
OR Zip Like 'NW9%'
OR Zip Like 'RG42%'
OR Zip Like 'RH8%'
OR Zip Like 'RM1%'
OR Zip Like 'RM10%'
OR Zip Like 'RM11%'
OR Zip Like 'RM12%'
OR Zip Like 'RM13%'
OR Zip Like 'RM14%'
OR Zip Like 'RM15%'
OR Zip Like 'RM16%'
OR Zip Like 'RM17%'
OR Zip Like 'RM18%'
OR Zip Like 'RM19%'
OR Zip Like 'RM2%'
OR Zip Like 'RM20%'
OR Zip Like 'RM3%'
OR Zip Like 'RM4%'
OR Zip Like 'RM5%'
OR Zip Like 'RM6%'
OR Zip Like 'RM7%'
OR Zip Like 'RM8%'
OR Zip Like 'RM9%'
OR Zip Like 'SE1%'
OR Zip Like 'SE10%'
OR Zip Like 'SE11%'
OR Zip Like 'SE12%'
OR Zip Like 'SE13%'
OR Zip Like 'SE14%'
OR Zip Like 'SE15%'
OR Zip Like 'SE16%'
OR Zip Like 'SE17%'
OR Zip Like 'SE18%'
OR Zip Like 'SE19%'
OR Zip Like 'SE1P%'
OR Zip Like 'SE2%'
OR Zip Like 'SE20%'
OR Zip Like 'SE21%'
OR Zip Like 'SE22%'
OR Zip Like 'SE23%'
OR Zip Like 'SE24%'
OR Zip Like 'SE25%'
OR Zip Like 'SE26%'
OR Zip Like 'SE27%'
OR Zip Like 'SE28%'
OR Zip Like 'SE3%'
OR Zip Like 'SE4%'
OR Zip Like 'SE5%'
OR Zip Like 'SE6%'
OR Zip Like 'SE7%'
OR Zip Like 'SE8%'
OR Zip Like 'SE9%'
OR Zip Like 'SG1%'
OR Zip Like 'SG10%'
OR Zip Like 'SG11%'
OR Zip Like 'SG12%'
OR Zip Like 'SG13%'
OR Zip Like 'SG14%'
OR Zip Like 'SG15%'
OR Zip Like 'SG16%'
OR Zip Like 'SG17%'
OR Zip Like 'SG18%'
OR Zip Like 'SG2%'
OR Zip Like 'SG3%'
OR Zip Like 'SG4%'
OR Zip Like 'SG5%'
OR Zip Like 'SG6%'
OR Zip Like 'SG7%'
OR Zip Like 'SG8%'
OR Zip Like 'SG9%'
OR Zip Like 'SL0%'
OR Zip Like 'SL1%'
OR Zip Like 'SL2%'
OR Zip Like 'SL3%'
OR Zip Like 'SL4%'
OR Zip Like 'SL5%'
OR Zip Like 'SL6%'
OR Zip Like 'SL7%'
OR Zip Like 'SL8%'
OR Zip Like 'SL9%'
OR Zip Like 'SL95%'
OR Zip Like 'SM1%'
OR Zip Like 'SM2%'
OR Zip Like 'SM3%'
OR Zip Like 'SM4%'
OR Zip Like 'SM5%'
OR Zip Like 'SM6%'
OR Zip Like 'SM7%'
OR Zip Like 'SS11%'
OR Zip Like 'SS12%'
OR Zip Like 'SS13%'
OR Zip Like 'SS14%'
OR Zip Like 'SS15%'
OR Zip Like 'SS16%'
OR Zip Like 'SS17%'
OR Zip Like 'SW1%'
OR Zip Like 'SW10%'
OR Zip Like 'SW11%'
OR Zip Like 'SW12%'
OR Zip Like 'SW13%'
OR Zip Like 'SW14%'
OR Zip Like 'SW15%'
OR Zip Like 'SW16%'
OR Zip Like 'SW17%'
OR Zip Like 'SW18%'
OR Zip Like 'SW19%'
OR Zip Like 'SW1A%'
OR Zip Like 'SW1E%'
OR Zip Like 'SW1H%'
OR Zip Like 'SW1P%'
OR Zip Like 'SW1V%'
OR Zip Like 'SW1W%'
OR Zip Like 'SW1X%'
OR Zip Like 'SW1Y%'
OR Zip Like 'SW2%'
OR Zip Like 'SW20%'
OR Zip Like 'SW3%'
OR Zip Like 'SW4%'
OR Zip Like 'SW5%'
OR Zip Like 'SW6%'
OR Zip Like 'SW7%'
OR Zip Like 'SW8%'
OR Zip Like 'SW9%'
OR Zip Like 'SW99%'
OR Zip Like 'TN14%'
OR Zip Like 'TN16%'
OR Zip Like 'TW1%'
OR Zip Like 'TW10%'
OR Zip Like 'TW11%'
OR Zip Like 'TW12%'
OR Zip Like 'TW13%'
OR Zip Like 'TW14%'
OR Zip Like 'TW15%'
OR Zip Like 'TW16%'
OR Zip Like 'TW17%'
OR Zip Like 'TW18%'
OR Zip Like 'TW19%'
OR Zip Like 'TW2%'
OR Zip Like 'TW20%'
OR Zip Like 'TW3%'
OR Zip Like 'TW4%'
OR Zip Like 'TW5%'
OR Zip Like 'TW6%'
OR Zip Like 'TW7%'
OR Zip Like 'TW8%'
OR Zip Like 'TW9%'
OR Zip Like 'UB1%'
OR Zip Like 'UB10%'
OR Zip Like 'UB11%'
OR Zip Like 'UB18%'
OR Zip Like 'UB2%'
OR Zip Like 'UB3%'
OR Zip Like 'UB4%'
OR Zip Like 'UB5%'
OR Zip Like 'UB6%'
OR Zip Like 'UB7%'
OR Zip Like 'UB8%'
OR Zip Like 'UB9%'
OR Zip Like 'W1%'
OR Zip Like 'W10%'
OR Zip Like 'W11%'
OR Zip Like 'W12%'
OR Zip Like 'W13%'
OR Zip Like 'W14%'
OR Zip Like 'W1A%'
OR Zip Like 'W1B%'
OR Zip Like 'W1C%'
OR Zip Like 'W1D%'
OR Zip Like 'W1F%'
OR Zip Like 'W1G%'
OR Zip Like 'W1H%'
OR Zip Like 'W1J%'
OR Zip Like 'W1K%'
OR Zip Like 'W1M%'
OR Zip Like 'W1S%'
OR Zip Like 'W1T%'
OR Zip Like 'W1U%'
OR Zip Like 'W1W%'
OR Zip Like 'W2%'
OR Zip Like 'W3%'
OR Zip Like 'W4%'
OR Zip Like 'W5%'
OR Zip Like 'W6%'
OR Zip Like 'W7%'
OR Zip Like 'W8%'
OR Zip Like 'W9%'
OR Zip Like 'WC1%'
OR Zip Like 'WC1A%'
OR Zip Like 'WC1B%'
OR Zip Like 'WC1E%'
OR Zip Like 'WC1H%'
OR Zip Like 'WC1N%'
OR Zip Like 'WC1R%'
OR Zip Like 'WC1V%'
OR Zip Like 'WC1X%'
OR Zip Like 'WC2%'
OR Zip Like 'WC2A%'
OR Zip Like 'WC2B%'
OR Zip Like 'WC2E%'
OR Zip Like 'WC2H%'
OR Zip Like 'WC2N%'
OR Zip Like 'WC2R%'
OR Zip Like 'WD1%'
OR Zip Like 'WD17%'
OR Zip Like 'WD18%'
OR Zip Like 'WD19%'
OR Zip Like 'WD2%'
OR Zip Like 'WD23%'
OR Zip Like 'WD24%'
OR Zip Like 'WD25%'
OR Zip Like 'WD3%'
OR Zip Like 'WD4%'
OR Zip Like 'WD5%'
OR Zip Like 'WD6%'
OR Zip Like 'WD7%'
group by CustomerID
order by Zip

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-11 : 10:07:58

you're not doing any aggregates so don't need to group .... comment the "Group By CustomerID" from your query. If a complete record is duplicating and duplication is needed to be removed use something like

SELECT DISTINCT * FROM
WHERE ....
....
Order by Zip

If this is not something you're looking for then provide sample data in form of Insert queries and the desired result you're looking for. This would help us to respond quickly.

e.g.
CREATE table #tmpTable (columnNames datatypes.....)
Insert into #tmpTable values (.....)

Desired tabular result

Cheers
MIK

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-03-11 : 10:15:25

Thanks! Got it now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources