Home » SQL & PL/SQL » SQL & PL/SQL » take the digit out of the string
take the digit out of the string [message #320626] Thu, 15 May 2008 12:38 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
[CODE}
Hi,
I would like to to know how to query that can take the digit out of the string and do comparison. The digit in the string does not have a fixed position.
I need to take the number and check if it is <=4.
Ex:

S4AB
LG4
L4AB
GR04FH
FH04
4PFH
AB1X
AC2X
A2CX
ABC4
ABC1
4
04AB
04
[/CODE]

Thanks,
Re: take the digit out of the string [message #320627 is a reply to message #320626] Thu, 15 May 2008 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the following ones:
http://www.orafaq.com/forum/t/118836/102589/
from today.

And you can also search as this has been asked many times.
Also have a look at regexp in SQL Reference.

Regards
Michel
Re: take the digit out of the string [message #320638 is a reply to message #320627] Thu, 15 May 2008 13:41 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks lot for your quick response.
Really appreciate.
I am using oracle 9i R2.
My select is following:

SELECT id FROM info WHERE group LIKE '%H%' AND grade ???

I want to filter that can take the digit out of the string and do comparison. The digit in the string does not have a fixed position.
I need to take the number and check if it is <=4.
It can contain 1 or 2 or 3 or 4 and in any order like:

A1XY
BXY2
BX3Z
...
I was trying to use TRANSLATE but throwing me error.
SELECT id FROM info WHERE group LIKE '%H%'
AND TRANSLATE(grade , 'S4NX', 4).

Thains in advance for your valuable help!
Re: take the digit out of the string [message #320640 is a reply to message #320638] Thu, 15 May 2008 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
translate let you remove all the characters you don't want as in the link I posted.
In this link I removed all figures and kept the alphabetic characters, you want the opposite, do so.

Regards
Michel
Re: take the digit out of the string [message #320645 is a reply to message #320640] Thu, 15 May 2008 14:14 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks once again.
How can you restrict for only digit < 4 means not above 4?
in your example:

SQL> select val, translate(val,'a1234567890','a') t from t;

so My sql will be follwoing, right?

SELECT id, grade FROM info WHERE group LIKE '%H%'
AND TRANSLATE(grade , 'S4NX', 4)??
How about if i want general sql for other grade?
Sorry if i misunderstood your explanation.

Thanks,
Re: take the digit out of the string [message #320649 is a reply to message #320645] Thu, 15 May 2008 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can you restrict for only digit < 4 means not above 4?

where "something" < 4

I wanted to remove all figures 0..9, I used "translate(val,'a1234567890','a')"
You want to remove other characters, you use translate...?

Regards
Michel
Re: take the digit out of the string [message #320652 is a reply to message #320649] Thu, 15 May 2008 14:41 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks much!
I tried following sql:

SELECT id, grade FROM info WHERE group LIKE '%H%' 
AND  grade IN (SELECT TRANSLATE(grade , '01234',  '0'  ) FROM info WHERE group LIKE '%H%')


for my following values filter:
S4AB
LG4
L4AB
GR04FH
FH04
4PFH
AB1X
AC2X
A2CX
ABC4
ABC1
4
04AB
04

But somehow its not giving me the desired results.

Thanks,
Re: take the digit out of the string [message #320655 is a reply to message #320652] Thu, 15 May 2008 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you are trying to achieve with
Quote:
grade IN (SELECT TRANSLATE(grade , '01234', '0' ) FROM info ...

Regards
Michel
Re: take the digit out of the string [message #320658 is a reply to message #320655] Thu, 15 May 2008 15:25 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
I am trying get the select based on my criteria which can take the digit out of the string and do comparison. The digit in the string does not have a fixed position.
I need to take the number and check if it is <=4.
It can contain 1 or 2 or 3 or 4 and in any order so i am trying to write the sql but looks like TRANSLATE doesn't seems to support in WHERE clause so i have added one more select clause.

Re: take the digit out of the string [message #320659 is a reply to message #320658] Thu, 15 May 2008 15:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TRANSLATE works anywhere in SQL statement.
First, forget the filter, just try to output the value you want to filter afterwards, for the moment just try to output it.

Of course, it would be easier to show you if you posted a test case, create table and insert statements.

Regards
Michel
Re: take the digit out of the string [message #320665 is a reply to message #320659] Thu, 15 May 2008 16:02 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Ok, see following create table and insert script:

CREATE TABLE INFO
(
  ID                  VARCHAR2(20)  NOT NULL,
  GROUP               VARCHAR2(4),
  GRADE               VARCHAR2(6),
  )
/

INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'1', 'SH', 'B1NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'2', 'LH', 'L4NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'3', 'SH', 'B2NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'4', 'NH', 'F0NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'5', 'FH', 'F1NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'6', 'SH', 'A1NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'7', 'SH', 'A2NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'8', 'FH', '2'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'9', 'CH', 'XXFH'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'10', 'GH', 'GR01FH'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'11', 'GH', 'GR01FH'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'12', 'GH', 'S4NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'13', 'GH', 'E0NX'); 
INSERT INTO INFO ( ID, GROUP, GRADE ) VALUES ( 
'14', 'GH', 'FH04'); 
COMMIT;


Now I am trying get the select based on my criteria which can take the digit out of the string and do comparison. The digit in the string does not have a fixed position.
I need to take the number and check if it is <=4.
It can contain 1 or 2 or 3 or 4 and in any order.

thanks,
Re: take the digit out of the string [message #320666 is a reply to message #320665] Thu, 15 May 2008 17:22 Go to previous messageGo to next message
jiltin
Messages: 44
Registered: September 2002
Member
Just try this, it may work out

select GRADE,TO_NUMBER(RTRIM(LTRIM(TRANSLATE(GRADE,'ABCDEFGHIJKLMNOPQRSTUVXYZabcdefghijklmnopqrstuvxyz',' '))))
from INFO
WHERE TO_NUMBER(RTRIM(LTRIM(TRANSLATE(GRADE,'ABCDEFGHIJKLMNOPQRSTUVXYZabcdefghijklmnopqrstuvxyz',' ')))) <= 4

Good Luck

Jiltin.com

Note: this translate has space ' ', but not null

Just I indicated '__space__' here is actual one char space.

select GRADE,TO_NUMBER(RTRIM(LTRIM(TRANSLATE(GRADE,'ABCDEFGHIJKLMNOPQRSTUVXYZabcdefghijklmnopqrstuvxyz','__space__'))))
from INFO
WHERE TO_NUMBER(RTRIM(LTRIM(TRANSLATE(GRADE,'ABCDEFGHIJKLMNOPQRSTUVXYZabcdefghijklmnopqrstuvxyz','__space__')))) <= 4
Re: take the digit out of the string [message #320674 is a reply to message #320665] Thu, 15 May 2008 18:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Your requirements are perfectly clear. But from what I can surmise, these may be what you are looking for??

SQL> select * from info;

ID                   GRP  GRADE
-------------------- ---- ------
1                    SH   B1NX
2                    LH   L4NX
3                    SH   B2NX
4                    NH   F0NX
5                    FH   F1NX
6                    SH   A1NX
7                    SH   A2NX
8                    FH   2
9                    CH   XXFH
10                   GH   GR01FH
11                   GH   GR01FH
12                   GH   S4NX
13                   GH   E0NX
14                   GH   FH04

14 rows selected.

SQL> select * from info
  2  where nvl(translate(grade, ' 1234', ' '), '0') != grade;

ID                   GRP  GRADE
-------------------- ---- ------
1                    SH   B1NX
2                    LH   L4NX
3                    SH   B2NX
5                    FH   F1NX
6                    SH   A1NX
7                    SH   A2NX
8                    FH   2
10                   GH   GR01FH
11                   GH   GR01FH
12                   GH   S4NX
14                   GH   FH04

11 rows selected.


BTW...next time you may want to post VALID sample code.

[Updated on: Thu, 15 May 2008 18:23]

Report message to a moderator

Re: take the digit out of the string [message #320691 is a reply to message #320666] Thu, 15 May 2008 22:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jiltin,
You posted 44 messages and don't know how to format code.
I recommend you to read OraFAQ Forum Guide, "How to format your post?" section, this will prevent you from explaining what is a space.

By the way, if you carefully read the topic you'll see I tried to lead OP to the solution by himself and not to give him a solution (as specified in the guide above).

Regards
Michel
Re: take the digit out of the string [message #320864 is a reply to message #320666] Fri, 16 May 2008 08:50 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Jiltin.
Your both sql throwing invalid number errors.

Re: take the digit out of the string [message #320867 is a reply to message #320864] Fri, 16 May 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you find one by yourself?

Regards
Michel
Re: take the digit out of the string [message #320868 is a reply to message #320674] Fri, 16 May 2008 08:52 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
ebrian,
Thank you very much providing valuable helps and tips, really appreciate your efforts.

It works fine.

Regards,
poratips
Re: take the digit out of the string [message #320882 is a reply to message #320868] Fri, 16 May 2008 10:24 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks once again.
There are problems with this sql as if it is F9200 then it making F900.
any solution for this?

Thanks,
Re: take the digit out of the string [message #320883 is a reply to message #320882] Fri, 16 May 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
any solution for this?

What is your opinion?

Regards
Michel
Re: take the digit out of the string [message #320890 is a reply to message #320882] Fri, 16 May 2008 10:42 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
poratips wrote on Fri, 16 May 2008 11:24

There are problems with this sql as if it is F9200 then it making F900.

What's "it" ??

I provided a SELECT, which doesn't change anything. If you are talking about consecutive numbers (ie. 92), then you should have specified that in your requirements and sample data.

[Updated on: Fri, 16 May 2008 10:45]

Report message to a moderator

Previous Topic: data from 3 tables and last row (merged)
Next Topic: alter tablespace test_tbs read only hangs
Goto Forum:
  


Current Time: Thu Dec 08 00:31:55 CST 2016

Total time taken to generate the page: 0.14420 seconds