Gowtham Ramamoorthy Gowtham Ramamoorthy - 6 months ago 21
SQL Question

How to fetch an ID from the table using replace,substring charindex in sql server 2012?

I have two tables(#temp1 and #temp2) as shown in the below code and i'm trying to get the matching static ID for the values present in #temp2 table but there is problem wiht the below code such that it returns null staticID value.

I'm not sure where its getting wrong can someone let me know whats the mistake im doing in the below code ?

BEGIN

CREATE TABLE #TEMP2
(
STATICNAME Varchar(160),
)

INSERT INTO #TEMP2 VALUES ('Per capita disappearance, carcass weight(456)'),('Production(286)')
CREATE TABLE #TEMP1
(
IDNUM INTEGER IDENTITY(1,1),
STATISTICNAME VARCHAR(256),
STATICID INTEGER
)

INSERT INTO #TEMP1 VALUES ('Per capita disappearance, carcass weight','144'),('Production','143')

Select LTRIM (RTRIM (REPLACE (T2.STATICNAME,SUBSTRING(T2.STATICNAME, CHARINDEX('(',T2.STATICNAME,0), LEN(T2.STATICNAME)-(CHARINDEX('(',T2.STATICNAME,0)-1)), ''))),T1.STATICID
from #TEMP2 T2 LEFT JOIN #TEMP1 T1 ON T2.STATICNAME = T1.STATISTICNAME
DROP TABLE #TEMP1
DROP TABLE #TEMP2
END

Answer

#temp1 table's STATISTICNAME values
Per capita disappearance, carcass weight,
Production

are not matching with #temp2 table's STATISTICNAME values
Per capita disappearance, carcass weight(456),
Production(286).

Since using the LEFT JOIN so obviously the left table #temp data will come, so the STATICID column from #temp1 is returning NULL

If you want to do the partial search with the columns use the LIKE operator. So your LEFT JOIN block will be:

LEFT JOIN #TEMP1 T1 ON T2.STATICNAME LIKE T1.STATISTICNAME + '%'