-
query to get partial data in a column
hello
need help to get part of description column
tablerders
orderid and description
data is
(1,hdmi cable 1cd)
(2,hdmi cable 1dc)
(3,logitech mouse 1ab)
i need to query the description but it should show only from 1
1,1cd
2,1dc
3,1ab
how do i get that
thanks for your help
-
Is it always a 1 ? If so, the following should work:
Code:
SQL> select * from orders;
ORDERID DESCRIPTION
---------- -------------------------
1 hdmi cable 1cd
2 hdmi cable 1dc
3 logitech mouse 1ab
SQL> select orderid,
2 regexp_substr(description, '1[^ ]*$') description
3 from orders;
ORDERID DESCRIPTION
---------- -------------------------
1 1cd
2 1dc
3 1ab
-
Hi
Can you please explain the regular expresssion
'1[^ ]*$'
I understand
* -------------> match any number of characters
$-------------->End of line
1-------------->Number 1
what is [^ ] ?
regards
Hrishy
-
* --> will match ZERO or more characters of the preceding character/expression
[ ] --> indicates a character list and the ^ is used to negate the character list
(ie. match ALL characters NOT IN this list).
therefore, [^ ]* will match ZERO or more characters EXCEPT spaces
-
THANK YOU for your your help and time ..
not sure what i was thinking ,its supposed to be T instead of 1 and yes it always starts with T and we have lot of "," or "-" in between description but not after T.Also we have spaces like T cd or T DC
do we need to use different expression if its charachter instead of a number..
SQL> select * from orders;
ORDERID DESCRIPTION
---------- -------------------------
1 hdmi cable Tcd
2 hdmi cable Tdc
3 logitech mouse Tab
Last edited by prodadmin; 01-06-2010 at 10:36 AM.
Reason: explian
-
The following should work based on that assumption:
Code:
ltrim(regexp_substr(description, ' T.*$'))
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|