CODEWITHSUNDEEP
sqlpostgresql
VHanded
VHanded

Reputation: 2089

Query a string column

I have a column, call 'expected_salary'. By the name, it suppose to be some numerical value. But I would like it to also hold value such as "competitive", "commission based" etc, so, it became a string type.

Now the dumb problem is, I would like to query it, like, SELECT all rows where expected_salary >= 3000. Of course, it won't work with a normal numeric comparison.

Before I convert the 'expected_salary' to integer type, and created an extra column call 'other_salary', I would like to know, if there is better solution.

Upvotes: 0

Views: 67

Answers (4)

Jouven
Jouven

Reputation: 116

In the where add "and expected_salary not in" with the values that can't be compared to numbers.

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21885

A guess:

create table salaries ( 
    expected_salary text
    );

insert into salaries values ('1000'),('1500,comment'),('3000,comment'),('3500'),
                                        ('comment'),('4000'),('4500,comment');

you can use substring() function to extract Integer from a string 

select * from (
select substring(expected_salary FROM '[0-9]+')::int sal from salaries
) t where sal >=3000;

Upvotes: 0

Deepak Mishra
Deepak Mishra

Reputation: 3183

You can have the delimited value.

For example:

for expected_salary='3000, salary comments'.

You can try below query.

SELECT *
FROM table
WHERE CAST(SUBSTRING(expected_salary, 0, PATINDEX('%,%',expected_salary)) as INT) >= 3000

Upvotes: 0

wallyk
wallyk

Reputation: 57774

Sure, create additional columns, probably booleans, which are false by default. is_commissioned, is_competitive, etc.

Then the proper query is

SELECT *
FROM table
WHERE expected_salary >= 3000 AND is_competitive

Upvotes: 3

Related Questions