SQL query help - Oracle syntax

[pumpkin]

column A has : 

barackobama

43..BUYER\.7020\.1
67.MP.PK\.2211\.E8
128.B.RAHUL\.1080\.E8
1284..C.MODI\.abcd\.TD\

i want output 

column a:

barackobama

43

67

128

1284
 

i want the starting numbers before '.'  if it doesn't has '.' it need to return the value in the column

[mtkr]

T-sql lo...

select col from table
where Left(columnName,1) not IN ('0','1','2','3','4','5','6','7','8','9')

union

select Left (Col, charindex('.', col) - 1) from table
where Left(columnName,1) IN ('0','1','2','3','4','5','6','7','8','9')

aa numbers ki somethng lyk

where col like [0-9] ala raayochu..

oracle lo charindex is INSTR anta so...

select Left (Col, instr(col,'.') - 1) from table
where Left(columnName,1) IN ('0','1','2','3','4','5','6','7','8','9')

 

srry bro.. naa daantlo SQL ledu.. so nt testd this...

 

@pumpkin

[pumpkin]

24 minutes ago, mtkr said:

T-sql lo...

select col from table
where Left(columnName,1) not IN ('0','1','2','3','4','5','6','7','8','9')

union

select Left (Col, charindex('.', col) - 1) from table
where Left(columnName,1) IN ('0','1','2','3','4','5','6','7','8','9')

aa numbers ki somethng lyk

where col like [0-9] ala raayochu..

oracle lo charindex is INSTR anta so...

select Left (Col, instr(col,'.') - 1) from table
where Left(columnName,1) IN ('0','1','2','3','4','5','6','7','8','9')

 

srry bro.. naa daantlo SQL ledu.. so nt testd this...

 

@pumpkin

it didnot work

[mtkr]

10 minutes ago, pumpkin said:

it didnot work

bro.. did not wrk ante!!?? query error aa!! r success ai results yield cheyyatleda!!??

Left badulu SUBSTR tho try cheiii...

[pumpkin]

3 minutes ago, mtkr said:

bro.. did not wrk ante!!?? query error aa!! r success ai results yield cheyyatleda!!??

Left badulu SUBSTR tho try cheiii...

kavalsini results ravadam ledu 

 

this is the query i am running

 

select xx.NAME from table xx
where substr(xx.NAME,1) not IN ('0','1','2','3','4','5','6','7','8','9')
union
select substr (xx.NAME, instr(xx.NAME,'.') - 1) from table xx
where substr(xx.NAME,1) IN ('0','1','2','3','4','5','6','7','8','9')

[4Vikram]

6 minutes ago, pumpkin said:

kavalsini results ravadam ledu 

 

this is the query i am running

 

select xx.NAME from table xx
where substr(xx.NAME,1) not IN ('0','1','2','3','4','5','6','7','8','9')
union
select substr (xx.NAME, instr(xx.NAME,'.') - 1) from table xx
where substr(xx.NAME,1) IN ('0','1','2','3','4','5','6','7','8','9')

kwalling @JollyBoy @kasi 

[JollyBoy]

idi basic nayana.. google etikina dorukutadi 

[pumpkin]

1 minute ago, JollyBoy said:

idi basic nayana.. google etikina dorukutadi 

i did i couldnt .. if possible can u let me know

[johny4]

delete * from dbname iyyu, antha set aithadhi adhe

[mtkr]

try substr(xx.NAME,1,1)

[JollyBoy]

select substr(col,1,NVL(Instr(Col,’.’),len(col))) 

[mtkr]

chunks lo test cheii..

frst aa not in then in...

a eadina id or diff col tho filter cheii.... to chk results

[ayyababoi]

SELECT

CASE WHEN Col1 like '%.%' THEN LEFT(Col1, CHARINDEX('.', Col1)-1)

ELSE Col1

END

FROM Table

[mtkr]

3 minutes ago, ayyababoi said:

SELECT

CASE WHEN Col1 like '%.%' THEN LEFT(Col1, CHARINDEX('.', Col1)-1)

ELSE Col1

END

FROM Table

yaap.. idi wrk avuthadi..

kani col data eadina city laaga unte like ft.lauderdale appudu pothadi !!!!

[JollyBoy]

4 minutes ago, ayyababoi said:

SELECT

CASE WHEN Col1 like '%.%' THEN LEFT(Col1, CHARINDEX('.', Col1)-1)

ELSE Col1

END

FROM Table

 

Just now, mtkr said:

yaap.. idi wrk avuthadi..

kani col data eadina city laaga unte like ft.lauderdale appudu pothadi !!!!

akada oracle sami..