Search for a percentage value in string using contains

Question

Search for a percentage value in string using contains

gjorgez

SSC Journeyman

Points: 83
More actions
May 6, 2015 at 3:43 am

#317300

Interest rate has been stored in comments column along with other information ( e.g. mike's student loan is 5% and car payment is $ 150). I need to extract 5% using Contains .. Why Contains? because it's a 1.7 m rows dataset and searching for fours specific interest rate values (e.g. 3%, 9%, 12% and 15%)
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Please post table scripts, sample data and expected output. See this page for instructions: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

-- Gianluca Sartori

gjorgez SSC Journeyman Points: 83 More actions · Answer 2

Interest rate has been stored in comments column along with other information ( e.g. mike's student loan is 5% and car payment is $ 150). I need to extract 5% using Contains .. Why Contains? because it's a 1.7 m rows dataset and searching for fours specific interest rate values (e.g. 3%, 9%, 12% and 15%)

Thanks

+--+

| PK_ID | Comments | Desired_Output |

+-------+-----------------------------------------+----------------+--+--+--+--+--+--+--+--+

| 1 | Mike's student loan 5% car payment $150 | 5%

| 2 | Credit card 9% $300 income $23000 | 9%

| 3 | Mortgage 3% Financial aid | 3%

+-------+-----------------------------------------+----------------+--+--+--+--+--+--+-

Desired Outpout

1. 5%

2. 9%

3. 3%

I tried this query but it didn't work

Select PK_ID,Comments

From Finanaid

where Contains(Comments,N'"5%" or "9%", "3%"')

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

For those trying to answer:

Sample data:

CREATE TABLE #Finanaid(

PK_ID INT NOT NULL PRIMARY KEY

, Comments VARCHAR(39)

, Desired_Output VARCHAR(2)

);

INSERT INTO #Finanaid(PK_ID,Comments,Desired_Output) VALUES (1,'Mike''s student loan 5% car payment $150','5%');

INSERT INTO #Finanaid(PK_ID,Comments,Desired_Output) VALUES (2,'Credit card 9% $300 income $23000','9%');

INSERT INTO #Finanaid(PK_ID,Comments,Desired_Output) VALUES (3,'Mortgage 3% Financial aid','3%');

Desired output

+-------+-----------------------------------------+----------------+

| PK_ID | Comments | Desired_Output |

+-------+-----------------------------------------+----------------+

| 1 | Mike's student loan 5% car payment $150 | 5% |

| 2 | Credit card 9% $300 income $23000 | 9% |

| 3 | Mortgage 3% Financial aid | 3% |

+-------+-----------------------------------------+----------------+

-- Gianluca Sartori