Deleting everything after a certain word in a SQL string

Question

I have found statements that delete everything after a certain character. How do i delete everything after a word even though the length after that word may vary.

11000 PLACIDA RD BLDG 15 UNIT 1504
17200 ACAPULCO RD BLDG 1

I want to delete everything after BLDG

Not really, Im working with over 7 million unique addresses. I am trying to delete out buidling/unit/apartment numbers — ObiWanCannoli, Jun 23 '16 at 15:50
Didn't you post a question that was almost exactly the same yesterday? http://stackoverflow.com/questions/37976762/using-wildcards-in-sql-to-delete-part-of-a-string — John Specko, Jun 23 '16 at 16:12
@JohnSpecko This is similar but different enough to class as a new question. — DavidG, Jun 23 '16 at 16:16
Yeah but I didnt receive as nearly as much feedback or help. Thanks for the help guys — ObiWanCannoli, Jun 23 '16 at 16:18

DavidG · Accepted Answer · 2016-06-23T16:09:49.417

You can use CHARINDEX to find the position of a string inside another string:

CHARINDEX('BLDG', ColumnName)

Then use LEFT to only take everything up to that point:

SELECT LEFT(ColumnName, CHARINDEX('BLDG', ColumnName))
FROM Table

And finally, since you want to include the BLDG text, you need to add 3 to the position (i.e. length of the string-1):

SELECT LEFT(ColumnName, CHARINDEX('BLDG', ColumnName)+3)
FROM Table

If you want to also delete the BLDG word, then subtract 1 instead:

SELECT LEFT(ColumnName, CHARINDEX('BLDG', ColumnName)-1)
FROM Table

score 0 · Answer 2 · answered Jun 23 '16 at 16:13

This worked for me.

CREATE TABLE #DeleteString ( AddressB4 VARCHAR(255) );

INSERT  INTO #DeleteString
        ( AddressB4 )
VALUES  ( '11000 PLACIDA RD BLDG 15 UNIT 1504'  -- AddressB4 - varchar(255)
          ),
        ( '17200 ACAPULCO RD BLDG 1'  -- AddressB4 - varchar(255)
          );
DECLARE @SearchStrng VARCHAR(255)= 'BLDG';
SELECT  REPLACE(AddressB4,
                SUBSTRING(AddressB4,
                          CHARINDEX(@SearchStrng, AddressB4)
                          + LEN(@SearchStrng), LEN(AddressB4)), '') AS FilteredAddress
FROM    #DeleteString;

Deleting everything after a certain word in a SQL string

2 Answers2