SQL statement

[rkonda99]

I have the following (table and data).

Table: PROD
Column: Serial (Varchar2)
Data:
29873-455
29873-456
29873-457
9R599999-000
9R599999-001
9R599999-003
9R599999-002
9R599976-023
9R599976-024
9R599976-025
234-001
234-004
234-002

I need to select the distinct values (prior to the hyphen '-' from
this column. The result should be as follows:

29873
9R599999
9R599976
234

Could you please suggest me the SQL statement.
Thanks.

[ales]

select distinct(substr(serial,1,instr(serial,'-')-1)) from prod;

[rkonda99]

Thanks you so much for your quick reply.
Along with the serials mentioned eralier, I have another set of serials (with no hyphen) in the table as follows:

2346
2345
2347
2378

When I run your select statement, I am not getting the above values back. I am getting the distinct values among the serials that have hyphen only. Please clarify.
Thanks.

[rkonda99]

I figured it out based on the clue from the earlier reply. Thank you so much.
Here is the code:
select substr( serial,1,decode((instr(serial,'-')-1),0,10,-1,10,(instr(serial,'-')-1)) )from prod

[klongid]

I have the following table:

TABLE NAME = T1
FIELD NAME = CKT
FORMAT = VARCHAR(53)

/TXNU/887037//LB
/TXNU/889192//LB
/TXNU/894980//LB
05.TXNU.323955. .GTEW. ATN 562TX11587
05.TXNU.323957. .GTEW. ATN 562TX11587
05.TXNU.347434. .GTEW. ATN 909TX13041
- SBN=720-874-0264-975

I would like to create a field which would eliminate any special characters ('/', '.', '-', '=') and spaces in the CKT field. Below is what I would like to see:

TXNU887037LB
TXNU889192LB
TXNU894980LB
05TXNU323955GTEWATN562TX11587
05TXNU323957GTEWATN562TX11587
05TXNU347434GTEWATN909TX13041
SBN7208740264975

Thanks in advance.

[B.Anand]

Here is the answer for your query.

select ltrim(rtrim(translate('45343-STE/453%.001','-/%.',' '))) from dual;

Hope this will help you..

Thanks.

[OCPIP]

------------
select ltrim(rtrim(translate('45343-STE/453%.001','-/%.',' '))) from dual;
--------------------

Translate will put an extra space in these characters so use replace to remove the space.

select trim(replace(translate('45343-STE/453%.001','-/%.',' '),' '))
from dual;

--Anurag

[jmodic]

Originally posted by OCPIP
Translate will put an extra space in these characters so use replace to remove the space.

No need for an additional function (REPLACE) to avoid this problem. And no need to use LTRIM/RTRIM/TRIM also. Why not simply use a single TRANSLATE function:

select translate(' 45343-STE/453%.001 ','1-/%.,= ','1') from dual;