Search a SQL table for data with literal percent % signs
I need to search a column for Terms, that is: "3% 15th/Net 30".
select * from POP10300 where Tems ='3% 15th/Net 30'
This gives me a series of rows. I need only "3% 15th/Net 30".
I used the brackets, "\", but nothing worked.
Whats the correct way to search for data that has a percent sign?
select * from POP10300 where Tems ='3% 15th/Net 30'
This gives me a series of rows. I need only "3% 15th/Net 30".
I used the brackets, "\", but nothing worked.
Whats the correct way to search for data that has a percent sign?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select column_name
from table_name
where substr(column_name, 2, 1) = "%"
this will return all values of cloumn_name in table_name that has a "%" as the 2nd character in the string.
like RiteshShah said...
you can't use LIKE because then the percentage will be interpritted as a wildcard.
if you expect to find the "%" in the 2nd character of the string then you can use the logic I provided. Beware I'm not sure of the exact implementation substr for the DBMS in question.
otherwise, you know what... you could select all values from the table and read them into an array of strings, then search each string for whatever character you like, but this means using a programming language (SQL cannot do this).
RiteshShah,
isn't there a way to search a string for a substr if he reads all the values into a cursor in a stored procedure?
from table_name
where substr(column_name, 2, 1) = "%"
this will return all values of cloumn_name in table_name that has a "%" as the 2nd character in the string.
like RiteshShah said...
you can't use LIKE because then the percentage will be interpritted as a wildcard.
if you expect to find the "%" in the 2nd character of the string then you can use the logic I provided. Beware I'm not sure of the exact implementation substr for the DBMS in question.
otherwise, you know what... you could select all values from the table and read them into an array of strings, then search each string for whatever character you like, but this means using a programming language (SQL cannot do this).
RiteshShah,
isn't there a way to search a string for a substr if he reads all the values into a cursor in a stored procedure?
>>Please excuse, but I didn't quite understand.<<
all I can say is, if you are looking for exact search with "=" operator, you don't need to think anything about that, it should work as it is. like:
select * from POP10300 where Tems ='3% 15th/Net 30'
if you are not getting any data with above filter than I strongly suggest you check space and character, you are not giving exact word.
all I can say is, if you are looking for exact search with "=" operator, you don't need to think anything about that, it should work as it is. like:
select * from POP10300 where Tems ='3% 15th/Net 30'
if you are not getting any data with above filter than I strongly suggest you check space and character, you are not giving exact word.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes shru_0409, that's the best thing a sample data.
Here is the Column TERMS contents:
Terms
-------------------
Net 30
1% 8 dias
3% 2th/Net 10
3% 15th/Net 30 <------------ I want to select just this line to display
1% 15 dias
Net 10
I just want to find "3% 15th/Net 30", the 4rth row; the result should display just one row = "3% 15th/Net 30"
When I use a select as RiteshShah indicate I get all that starts with "3". It doesn't work since % is consider as wildcard.
Here is the Column TERMS contents:
Terms
-------------------
Net 30
1% 8 dias
3% 2th/Net 10
3% 15th/Net 30 <------------ I want to select just this line to display
1% 15 dias
Net 10
I just want to find "3% 15th/Net 30", the 4rth row; the result should display just one row = "3% 15th/Net 30"
When I use a select as RiteshShah indicate I get all that starts with "3". It doesn't work since % is consider as wildcard.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
have a look: working fine
create table te
(
terms varchar(50)
)
insert into te
select 'Net 30' union all
select '1% 8 dias' union all
select '3% 2th/Net 10' union all
select '3% 15th/Net 30' union all
select '1% 15 dias' union all
select 'Net 10'
GO
select * from te where terms='3% 15th/Net 30'
try below query
select * from POP10300 where Tems like '3\% 15th/Net 30' ESCAPE '\';
ASKER
Sorry Guys, the simple select does work "...where terms='3% 15th/Net 30'"
I was forgetting that the table repeats its entry (has duplicates). Prior placing the question I saw the result was not display the line and it really had lots of lines,
RiteshShah, was correct from the start and I should have known it, I just confused the wildcard "%" with "="
Thanx all
I was forgetting that the table repeats its entry (has duplicates). Prior placing the question I saw the result was not display the line and it really had lots of lines,
RiteshShah, was correct from the start and I should have known it, I just confused the wildcard "%" with "="
Thanx all
ASKER
I'll split the points equally since I mislead the question. Sorry for the confusions. If my point assignment is incorrect, please advice; first time this happens to me and I want to be just.
ASKER
The database has a column that I need to find. Its content is '3% 15th/Net 30'
How do I search the table and find the value?
In essence, I need to find literally data that may have % sign in their contents. I'm not looking for LIKE or the function of wildcard "%". I need to find data that has the character & in their contents.