TSQL to append data in column

Question

I have a column named ADDRESS that has, as one guessed, addresses in it in the format of 100 NE 26th ST. So basically its the street address. What I want to do is add BLK after the numerical so the end result is 100 BLK NE 26th ST. Now I ran a simple script that changed 00 to 00 BLK, however when there was a street name of say 100th ST, it blew it up.

So basically is there a way to add BLK right after the house number and before the street name??

Answer 1

DECLARE @Address TABLE(Address1 VARCHAR(MAX))

INSERT INTO @Address VALUES
('100 NE 26th ST.'),
('100th NE 26th ST.'),
('P.O. Box 133'),
(''),
(NULL)

SELECT CASE WHEN Address1 LIKE '[0-9]%' THEN 
    STUFF(Address1,CHARINDEX(' ',Address1),1,' BLK ')
    ELSE Address1 END
FROM @Address

Gordon is right about stuff.. but you might want to check for po box or blanks.. or null

Edit

Based on your comments.. to update your People table you would simply do.

UPDATE People
SET Address1 = CASE WHEN Address1 LIKE '[0-9]%' 
                    THEN STUFF(Address1,CHARINDEX(' ',Address1),1,' BLK ')
                    ELSE Address1 END

Answer 2

One possibility is that you want to add "BLK" where the first space is. You can accomplish this with:

select stuff(address, charindex(' ', address), 1, ' BLK ')

That logic works for the situations you describe in the question, but it may not fully generalize to what you really need -- depending on other eccentricities in the addresses.