4

Trying to pull just the Street addresses out of this data:

CREATE TABLE foo(Places varchar(50));
INSERT foo(Places) VALUES
('MARKET @123 NORTH RD'),
('HARDWARE @600 Main RD ;W1'),
('MAIN AV / NORTH RD'),
('500 NORTH RD @LIBRARY'),
('500 ANYSTREET ; *** SIDEWALK****'),
('MARKET @123 NORTH RD'),
('700 ANYSTREET'),
('(088.12345,088.12345) ;75 SOUTH RD');

I've trying to do use CASE, SUBSTRING and (CHAR/PAT)INDEX... But I'm just not doing this correctly.

Select 
[Address] = CASE WHEN PATINDEX('%@[0-9]%' , Places) = 0 THEN     (SUBSTRING(Places, 0, CHARINDEX('@',Places)))
                WHEN PATINDEX('%@[a-z]%' , Places) = 0 THEN     (SUBSTRING(Places, CHARINDEX('@',Places) + 1, LEN(Places)))
                WHEN PATINDEX('%;[0-9]%' , Places) = 0 THEN (SUBSTRING(Places, CHARINDEX(';',Places) + 1, LEN(Places)))
                WHEN PATINDEX('[0-9]%' , Places) = 1 THEN (SUBSTRING(Places, 0, CHARINDEX(';',Places))) 
                WHEN PATINDEX('%/%' , Places) > 0 THEN Places END

From dbo.foo

What is the better way to grab that data? I've looked at string selects for finding email addresses, but seem to fall flat again when dealing with the ';'

TIA!

edit for desired output, table containing:

123 NORTH RD 
600 Main RD
MAIN AV / NORTH RD
500 NORTH RD 
500 ANYSTREET
123 NORTH RD 
700 ANYSTREET
7575 SOUTH RD
  • Can you provide expected output? – Erik Darling Feb 23 '17 at 23:24
  • A table of just the street addresses: 123 NORTH RD, 600 Main RD, MAIN AV / NORTH RD, 500 NORTH RD, 500 ANYSTREET, 123 NORTH RD, 700 ANYSTREET,7575 SOUTH RD – unsigned1138 Feb 24 '17 at 14:08
  • You should edit your question to add that in. The formatting in comments stinks. – Erik Darling Feb 24 '17 at 14:09
  • I hear ya. Added. – unsigned1138 Feb 24 '17 at 14:26
  • Regex would make short work of this I'm sure. But I think even a more traditional Sql solution is possible. It usually helps me when I put into English exactly what I'm trying to accomplish. – paulbarbin Feb 24 '17 at 15:01
4

Try this.

;With cte as
(
Select 
    places,
    StartHere = 
        CASE 
            WHEN PATINDEX('%@[0-9]%', places) > 0 THEN PATINDEX('%@[0-9]%', places) + 1     -- @ plus number, start here
            WHEN PATINDEX('%;[0-9]%', places) > 0 THEN PATINDEX('%;[0-9]%', places) + 1     -- ; plus number, start here
            ELSE 0
        END
    ,EndHere = 
        CASE
            WHEN PATINDEX('%@[a-z]%', places) > 0 THEN PATINDEX('%@[a-z]%', places)   -- @plus letter, stop here        
            WHEN PATINDEX('%@[0-9]%', places) > 0 AND PATINDEX('%;%', places) > 0 THEN  PATINDEX('%;%', places)  --@ plus number stop on semicolon if there
            WHEN PATINDEX('%;[0-9]%', places) = 0 AND PATINDEX('%;%', places) > 0 THEN PATINDEX('%;%', places)   --no semi plus number, so stop on semicolon if there
            ELSE len(places) + 1
        END
FROM
    foo
)
    SELECT 
        places, 
        StartHere
        ,EndHere
        ,SUBSTRING(places, StartHere, EndHere - StartHere)
    FROM 
        cte
  • I'd add that I started with the concept of start and end and added each condition (not sure I added all possibilities, you might have to expand). But some possibilities just fell to the else of start at zero and end at end. I ended up needing only 5 conditions. – paulbarbin Feb 24 '17 at 15:52
  • This concept works on my test data as a subquery pretty well. I'm messing up the join for the production somehow though, not quite sure where I'm going wrong. I will keep messing with it, thank you for the pointer in the right direction. – unsigned1138 Feb 24 '17 at 23:51
3

Don't try to do that with T-SQL - RegEx is no match for the creativity of end users with a text field.

If you need to do it production-quality at scale, check out the Melissa Data SSIS components. (I have no relationship with that company at all - just heard excellent things about it from people who have to do this exact sort of thing for a living.)

  • I have no doubt this is a technically correct answer.... But at this time, I need to operate in T-SQL. As much as it doesn't look like it, the data in these fields is actually NOT free form enter. It arrives a few different ways, hence the formatting challenge. Maybe I should just take my best effort approach and make the case to move to an SSIS option when/if there are issues. – unsigned1138 Feb 24 '17 at 14:12
  • Sorry but Brent is right, RegEx is just not powerful enough to deal with the uncertainties. If I were in your situation and I couldn't use anything other than T-SQL alone (SSIS is exactly what I'd normally use), then I'd probably use a combination of RegEx and also a "Control" table where I'd have common expected values. Then I'd use something like SOUNDEX or DIFFERENCE between the control table and your data to try to get the best matches possible. – MguerraTorres Feb 24 '17 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.