Extract Only charcters from a String

Question

I have a column value like

lut00006300.txt  
sand2a0000300.raw

I need to extract only character data from above given column values. I tried the below query and was able to get the first three characters.

select filesize,
substring(Filename  FROM 1 FOR 3) AS Instrument from Collection;

Is there any approach to extract only the characters from the column value leaving the extensions

The results should be :

LUT  
SAND2A

What do you mean by "*only characters*"? `l`, `u`, `t`, `0`, `6`, `3`, `.`, `x`, `s`, `a`, `n`, `d`, `2`, `r` and `w` are all characters! — eggyal, Sep 06 '12 at 10:05
@hims056: So the results should be `luttxt` and `sandaraw`? Seems odd to me. — eggyal, Sep 06 '12 at 10:08
No I would like to retrieve only the character data before the extensions(.txt,.raw) Like LUT,SANDA2A — user1633295, Sep 06 '12 at 10:25

score 0 · Answer 1 · answered Sep 06 '12 at 10:28

0

I think below query will helps you.

select filesize,Filename from Collection where Filename REGEXP '[:alpha]';

answered Sep 06 '12 at 10:28

Thnks a lot but when i fire the above given query I got the output as 1 – user1633295 Sep 06 '12 at 10:42
@user1633295 look this link will help you http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server – Ravichandran Jothi Sep 06 '12 at 11:25

score 0 · Answer 2 · answered Sep 06 '12 at 10:37

0

SELECT
filesize,
UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Filename, '.', 1), '0', 1)) AS Instrument
FROM Collection;

This is a dirty solution, since you want to have the 2 in SAND2A.

2 Answers2