CODEWITHSUNDEEP
sql-server
Sanket J
Sanket J

Reputation: 187

Compare two columns (one varchar, one int)

I am constructing a temp table is sqlserver. Now in this temp table, I wanna check if one column integer value exists in another column's text field or not.

For ex. i have col. days which has values of 2,10,15,30... and so one multiple times and for each value, and another columns which has an entire text that has a description of set of rules and at the end, the timeline is 2 Calendar Days or 30 Calendar days or 10 Business Days which should match the integer column.

How do I compare the int value in the text matches in the rules text column?

For ex.

col1   col2
2      ....should happen....- 2 business days
4      ....should happen....- 4 business days
5      ....should happen....- 5 business days
6      ....should happen....- 6 business days
15     ....should happen....- 15 business days
30     ....should happen....- 30 business days

Upvotes: 0

Views: 1338

Answers (3)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SELECT *
FROM TEMP
WHERE col2 LIKE '%- '+cast(col1 as varchar)+' % days'

See SQLFIDDLE

Or may be : 

SELECT *,
       CASE WHEN col2 LIKE '%- '+cast(col1 as varchar)+' % days' 
            THEN 'Exists' 
            ELSE 'Not Exists' END AS "Exists"
FROM TEMP

See SQLFIDDLE

For msi77 :

Results:

| COL1 |                                    COL2 |     EXISTS |
|------|-----------------------------------------|------------|
|    2 |  ....should happen....- 2 calendar days |     Exists |
|    2 | ....should happen....- 20 calendar days | Not Exists |
|    4 |  ....should happen....- 4 calendar days |     Exists |
|    5 |  ....should happen....- 5 business days |     Exists |
|    6 |  ....should happen....- 6 business days |     Exists |
|   15 | ....should happen....- 15 business days |     Exists |
|  999 | ....should happen....- 00 business days | Not Exists |
|   30 | ....should happen....- 30 business days |     Exists |

Upvotes: 1

Kaf
Kaf

Reputation: 33809

You can filter out int from the string like below. (Based on few assumptions: only one '-' in the string before the number, number has left and right spaces)

declare @s varchar(100) = '...should happen....- 20 business days'

;with cte as (
    select right(@s,len(@s)-charindex('-',@s,0)- 1) as rightText
)
select left(rightText, charindex(' ', rightText,0))
from cte

Query would be like

;with cte as (
    select col1, col2,
           right(col2,len(col2)-charindex('-',col2,0)- 1) as rightText

    from yourTable
 )
 select col1,col2
 from cte
 where left(rightText, charindex(' ', rightText,0)) = col1

Upvotes: 2

Tricky12
Tricky12

Reputation: 6822

First, you can get the number from the varchar like this:

SUBSTRING(col2, CHARINDEX('-', col2) + 1, 2)

Then you can convert it into an INT like this:

CONVERT(INT, SUBSTRING(col2, CHARINDEX('-') + 1, 2))

SUBSTRING

CHARINDEX

CONVERT

Upvotes: 0

Related Questions