Identifying Repeating values in a string

[jalgoza]

The address field in my User table has data like:

1905 1905 S OAK ST
314 ROSSELL AVE
252 252 HIGH MEADOWS ST
1402 1402 TOWNSEND CT
2021 MAHAN AVE
347 347 N 7 AVE

As you see we have repeating House #s here. I would like to write a query to:

1.Give me addresses and its counts where the house # has been repeated.
2.Correct the repeating house # to have a single house # in the Street Address

Please help me out here.

[iburyak]

Try this:

1. Create test table and insert data:
[PHP]
create table table1 (address varchar(30))
insert into table1 values ('1905 1905 S OAK ST')
insert into table1 values ('314 ROSSELL AVE')
insert into table1 values ('252 252 HIGH MEADOWS ST')
insert into table1 values ('1402 1402 TOWNSEND CT')
insert into table1 values ('2021 MAHAN AVE')
insert into table1 values ('347 347 N 7 AVE')[/PHP]

2. Create function:

[PHP]CREATE FUNCTION fnSplit(@String nvarchar(4000), @Delimiter char(1))
RETURNS varchar(100)
AS

BEGIN
DECLARE @Results TABLE (Items nvarchar(200))

DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @DuplicateAddr varchar(50)

SELECT @INDEX = 1
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
Select @DuplicateAddr = Items
from @Results
group by Items
having count(*) > 1

RETURN @DuplicateAddr
END [/PHP]

3. Query table:

[PHP]Select *, Replace(address, DupAddress + ' ' + DupAddress, DupAddress) ResultAddress
From (
select address, dbo.fnSplit(address, ' ') DupAddress
from table1 ) a
Where Not DupAddress is null[/PHP]

Hope it helps.
Good Luck.