Search a SQL table for data with literal percent % signs

jana
jana used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
if you will use "=" than you will get exact search but yes, if you will use "LIKE" rather than "=" than "%" sign will be treated as wild card character.
you're expecting the % sign to be the 2nd or 3rd character... right?
so you need to use substr(str, 2, 1) or substr(str, 3, 1) = "%" in your where clause.

Author

Commented:
Please excuse, but I didn't quite understand.

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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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?
>>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.
HI,,
TRY THE BELOW QUERIES

SELECT * FROM POP10300 WHERE CHARINDEX('%',Tems )>0
SELECT * FROM POP10300 WHERE Tems LIKE '%!%%' ESCAPE '!'
could u send the sample data

Author

Commented:
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.
Ravi KallaTechnology Lead
Commented:
to search for a string "abc%def", you can use the following code snippet

SELECT column_name
FROM table_name
WHERE column_name LIKE 'abc\%def' ESCAPE '\';

Open in new window

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'

Open in new window

Ravi KallaTechnology Lead

Commented:
try below query

select * from POP10300 where Tems like '3\% 15th/Net 30' ESCAPE '\';

Open in new window

Author

Commented:
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

Author

Commented:
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.
split will be fine. no issue, glad your concept get clear!!!

Ritesh Shah

http://www.SQLHub.com

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial