Extract number from left side of string 1

[RustyAfro]

Below is some sample data that I need to extract the number (age), the M or F (gender) and the rest after the M or F.

The problem is that age is the first character for single digit ages, the first two chars for double digit ages, and the first three chars for triple digit ages (real old).

I'm trying to find that best Oracle SQL way of extracting these elements. Ideally I could just pad one or two "0"s in front of the single or two digit ages and then use a simple Substr to extract the different data components (casting the 001 to a Number).

I'm not sure how to dynamically pad it though or know if that is even possible.

Col
----
1MSIN
24FIOD
124MDFD

Any help would be greatly appreciated!

 

[Dagon]

Translate might be a better way to go:

select translate(string, '0ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0'), 
		        substr(translate(string,'a0123456789', 'a'), 1,1),
				substr(translate(string,'a0123456789', 'a'),2)
		from
		(select '1MSIN' as string  FROM DUAL
		 UNION ALL
	 select '24FIOD' as string FROM DUAL
         UNION ALL
	 select '124MDFD' FROM DUAL)

 

[RustyAfro]

whoa, just tried that out. Pretty clever!

Here's a star

 

[sem]

A bit shorter solution:

replacestring, ltrimstring, '1234567890'))

And suitable only for this specific case:

substr(lpadstring , 7 ,'0'), 1, 3)


Regards, Dima

http://seminihin.narod.ru