Find a string within a string in SQL Server 2016

Question

I am trying to write a query to find a string withing a string. In Oracle I used regexp_like but I don't see such function in SQL Server 2016. I have a table that has an address column such as:

ADDRESS
--------
345 E 149 ST NY NY
345 EAST 149 STREET NY NY
444 CHEST AVE NY NY
444 CHEST AVENUE NY NY

I want to write a query that search for 345 [E OR East] follow by 149. in the case of 444 chest, I want a query to search for 444 chest[ ave or avenue].

I have something like the following which doesn't work

select * from table1 where address like '345[EEAST] 149%'

Basically I want to tell the query to get any address that start with 345 E or 345 EAST follow by 149.

Can someone help me write a query for this? I know I can use OR clause with two different address but if I have multiple addresses with different pattern then OR clause will not be efficient method. I'm looking into using some type of regular expression.

Have a look at LIKE but you need to add the wildcard % for it to be any use. Otherwise try charindex or patindex. Thats what is on offer with SQL Server. — Dale K
– Dale K, Commented May 13, 2020 at 2:32

Venkataraman R · Accepted Answer · 2020-05-13 04:26:15Z

1

Unfortunately the amount of support for LIKE expression is very limited currently in SQL Server. Your requirement can be satisfied by below queries

DECLARE @table table(address VARCHAR(1000))

INSERT INTO @table
values
('345 E 149 ST NY NY')
,('345 EAST 149 STREET NY NY')
,('444 CHEST AVE NY NY')
,('444 CHEST AVENUE NY NY')


SELECT * FROM @table WHERE Address LIKE '345 E%149 ST% NY NY'

SELECT * FROM @table WHERE Address LIKE '444 CHEST AVE% NY NY'

Result Set

+---------------------------+
|          address          |
+---------------------------+
| 345 E 149 ST NY NY        |
| 345 EAST 149 STREET NY NY |
+---------------------------+


+------------------------+
|        address         |
+------------------------+
| 444 CHEST AVE NY NY    |
| 444 CHEST AVENUE NY NY |
+------------------------+

answered May 13, 2020 at 4:26

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2020-05-13 02:43:50Z

0

Use the LIKE operator, with separate conditions for each match:

SELECT *
FROM table1
WHERE ADDRESS LIKE '345 E 149 %' OR ADDRESS LIKE '345 EAST 149 %';

answered May 13, 2020 at 2:43

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

3 Comments

yoohoo Over a year ago

hi Tim, thanks for your reply. I am aware that i can use OR but as stated in my original post i can have different pattern of the same address. if i use OR then i have to use an OR for each different pattern of the same address. im looking for regular expression without using OR

Dale K Over a year ago

@yoohoo did you try LIKE '345 % 149 %'?

Tim Biegeleisen Over a year ago

Unfortunately, SQL Server has no native regex support.

Reza Jenabi · Accepted Answer · 2020-05-13 10:12:00Z

0

You can try this in WHERE Clause.

SELECT *
FROM   @T
WHERE  (
           1 = CASE 
                    WHEN ADDRESS LIKE '%345_E_149%' THEN 1
                    WHEN ADDRESS LIKE '%345_EAST_149%' THEN 1
                    WHEN ADDRESS LIKE '%444_CHEST_AVE%' THEN 1
                    WHEN ADDRESS LIKE '%444_CHEST_AVENUE%' THEN 1
               END
       )

edited May 13, 2020 at 10:12

Reza Jenabi

4,3491 gold badge34 silver badges36 bronze badges

answered May 13, 2020 at 4:05

Thiyagu

1,3401 gold badge8 silver badges16 bronze badges

Collectives™ on Stack Overflow

Find a string within a string in SQL Server 2016

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related