Changes spaces in string into OR statement

Question

I am trying to convert spaces from a string into an OR statement.

Declare @postcodes varchar(128)
SET @postcodes = '2130 2602' --this input will be postcodes separated by a space (I'm in Australia so the postcodes are only 4 numbers long)
SET @postcodes = REPLACE(@postcodes, ' ', ' OR postcode = ') --I know this isn't right but it shows what I am trying to achieve
SELECT *
FROM users
WHERE postcode = @postcodes

I know that the above query is really looking for users where

postcode = '2130 OR postcode = 2602'

I really want to find users where

postcode = '2130' OR postcode = '2602'

Any ideas or direction on what I should be googling?

Update

I ended up combining the methods suggested by Albin Sunnanbo and Falcon. The function method suggested by Will Hughes was a lot more elegant but I would prefer not to use functions in this particular installation.

Here is my final query with some added cleaning features.

Declare @postcodes varchar(128)
SET @postcodes = '2130 2602 2000'

--Postcode cleaning
SET @postcodes = REPLACE(@postcodes,';',' ') --removes ";"
SET @postcodes = REPLACE(@postcodes,',',' ') --removes ","
SET @postcodes = (LTRIM(RTRIM(@postcodes))) --trim white space
SET @postcodes = REPLACE(@postcodes,'  ',' ') --removes double spaces
SET @postcodes = REPLACE(@postcodes,'  ',' ') --removes double spaces again
SET @postcodes = '''' + REPLACE(@postcodes,' ',''',''') + '''' --adds ' to @postcodes

DECLARE @SQLString nvarchar(MAX);
SET @SQLString = N'
SELECT * 
FROM users
WHERE postcode IN(' + @postcodes + ')';

EXECUTE sp_executesql @SQLString;

SELECT @SQLString AS SQLString --This part serves no purpose, it just shows you what the query looks like

Is there a reason you can't do this in the code that provides the input? — , May 08 '12 at 04:19
It needs to be all done in SQL because of the current setup. Doing it in the code would be a lot easier. — Gregology, May 08 '12 at 04:22

score 3 · Answer 1 · edited May 23 '17 at 10:08

3

You can split strings using the answer to this Stack Overflow Question.

Having this function created, you can use it like so:

Declare @postcodes varchar(128)
SET @postcodes = '2130 2602'

SELECT *
FROM users
   JOIN dbo.Split(' ',@postcodes) postcodes 
   ON users.postcode = postcodes.s

edited May 23 '17 at 10:08

Community

1
1

answered May 08 '12 at 04:28

score 2 · Answer 2 · answered May 08 '12 at 04:30

I would build a string and execute with sp_executesql

Declare @postcodes varchar(128)
SET @postcodes = '2130 2602'
SET @postcodes = REPLACE(@postcodes, ' ', ',')

DECLARE @SQLString nvarchar(MAX);
SET @SQLString = N'SELECT *
FROM users
WHERE postcode IN (' + @postcodes ')';

EXECUTE sp_executesql @SQLString;

beware of SQL injection though.

score 2 · Accepted Answer · answered May 08 '12 at 05:35

2

Declare @postcodes varchar(128)
SET @postcodes = '2130 2602'  
SET @postcodes = CONCAT('"',REPLACE(@postcodes, ' ', '" OR postcode = "'),'"') -- Now it will make it "2130" OR postcode = "2602"-------- 

SELECT *
FROM users
WHERE postcode = @postcodes

Hope this helps

answered May 08 '12 at 05:35

Falcon

259
3
8

Thanks, this was the simplest solution. Cheers Falcon! – Gregology May 08 '12 at 06:01
Cheers back at ya mate! Always happy to help:) – Falcon May 08 '12 at 06:04

Changes spaces in string into OR statement

3 Answers3