SQL Code to parse out an address

I would suggest that you wouldn't be able to write something that works perfectly but you could write something which works to a specific set of rules. For example, how would you distinguish between the above and a guy that lives in 123AMainStreet where 123A Main Street is the address of a flat.

First thing you really need to do is specify EXACTLY what rules you want and go through them one by one.

Your example above - iterate thought the characters and move the characters to another string one by one putting a space before each capital. This would possibly not work as you'd require it for my example

Thanks cmhunty- unfortunately, the data coming in to me is a mix of some addresses having all caps, some all lower case, and some being a mix of caps/lower case. At this point, just being able to add a space between the house number and the street name is what I am looking for. I see your point with the 123A vs. 123, but that is something I will have to deal with at another time.

Thanks bhess1- what if I just want to put a space between the last number in the house number and the first letter of the street address? Is that something that is a bit more simple to code?

You can just remove the REPLACE statements to do that.  Also, you can replace part of the code, ending up with:

DECLARE FUNCTION dbo.ufn_ParseAddress (
    @addr varchar(255)
    )
RETURNS varchar(255)
AS
BEGIN
    DECLARE @ptr int
    DECLARE @ptr2 int

    SET @ptr = 0
    SET @Work = ''

    WHILE @ptr < Len(@addr)
    BEGIN
        SET @ptr2 = @ptr
        SET @ptr = @ptr + 1
        IF Substring(@addr, @ptr, 1) LIKE '[abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ]'
            SET @ptr = 255   -- exit out
    END

    IF @ptr2 < Len(@addr) AND @ptr2 > 0
    BEGIN
        SET @addr = LEFT(@Addr, @ptr2) + ' ' + Substring(@addr, @ptr2+1, Len(@addr))
    END

    RETURN @addr
END

Oops - remove the SET @Work line

Bhess1- that did the trick- thanks