Find specific word in column

Question

I am using SQL Server 2008.

My tables are :

Location

------------------------
 Id  |  LocationName
------------------------
  1  |  Bodakdev
  2  |  Thaltej Road
  3  |  Andheri East
  4  |  Noida Sector 2

Company

 --------------------------------------------------------------------------
   CId  |  Address                                            | LocationId
 --------------------------------------------------------------------------
   11   |   301, GNFC Infotower, Bodakdev,                    |    NULL
   12   |   307/308,Arundeep Complex                          |    NULL
   13   |   7 Krishana Dyeing Compund, Nagardas rd., Andheri  |    NULL
   14   |   B-23 ,Ground Floor,Sector 2                       |    NULL
 --------------------------------------------------------------------------

Currently LocationId in the Company table are null. If Address contains any location name then update LocationId.

For example, Address of CID - 11 contains Bodakdev then update LocationId 1, second example, Address of CID - 13 contains Andheri word then update LocationId 3.

Required output :

  CId  |  Address                                            | LocationId
 --------------------------------------------------------------------------
   11   |   301, GNFC Infotower, Bodakdev,                    |    1
   12   |   307/308,Arundeep Complex                          |    NULL
   13   |   7 Krishana Dyeing Compund, Nagardas rd., Andheri  |    3
   14   |   B-23 ,Ground Floor,Sector 2                       |    4
 --------------------------------------------------------------------------

I have tried using below query

SELECT   
   (LEN(Address) - LEN(REPLACE(Address, LocationName, '')) ) / LEN(LocationName) 

if Address contains Location Name then it will return number of occurrences otherwise it return 0.

But it will not give correct output. How can I do this? Thanks. Any suggestion would be appreciated.

Answer 1

Try following Query :

1.STEP1 : make one function which can split the sting by any character and return the output in table format .

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

2.STEP2 : use following query to get your desire output .

    DECLARE @LOCATION AS TABLE (ID INT ,NAME VARCHAR(MAX))  
DECLARE @COMPANY AS TABLE (CID INT , ADDRESS VARCHAR(MAX) , LOCATIONID INT)     

INSERT INTO @LOCATION VALUES(1,'Bodakdev')
INSERT INTO @LOCATION VALUES(2,'Thaltej Road')
INSERT INTO @LOCATION VALUES(3,'Andheri East')
INSERT INTO @LOCATION VALUES(4,'Noida Sector 2')

INSERT INTO @COMPANY VALUES(11,'301, GNFC Infotower, Bodakdev,' , NULL)
INSERT INTO @COMPANY VALUES(12,'307/308,Arundeep Complex' , NULL)
INSERT INTO @COMPANY VALUES(11,'7 Krishana Dyeing Compund, Nagardas rd., Andheri' , NULL)
INSERT INTO @COMPANY VALUES(11,'B-23 ,Ground Floor,Sector 2' , NULL)

UPDATE @Company
SET
LOCATIONID = B.ID
FROM @COMPANY AS A , @LOCATION AS B 
WHERE 
1 = CASE WHEN 
            (
                SELECT COUNT(*)
                FROM FNSPLIT(B.NAME , ' ')
                WHERE A.ADDRESS LIKE '%' + ITEM +  '%'
            ) > 0 THEN 1 ELSE 0 END

This is the one way to do it . we can do it using full text searching also.