Instr, Locate or Splite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Instr, Locate or Splite

de f
I'm trying to determine how (if possible without writing
extension) to split a single column by a character value - for
example

Column AgeRange has values such as 20-30, 40-50

I'd like to do something like...

select substr(agerange,1,instr(agerange,'-')-1) as StartAge,
substr(agerange,instr(agerange,'-')+1) as EndAge

I don't see an instr function supported in sqlite - any ideas?

________________________________________________
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag
Reply | Threaded
Open this post in threaded view
|

Re: Instr, Locate or Splite

Dennis Cote
debra f wrote:

>I'm trying to determine how (if possible without writing
>extension) to split a single column by a character value - for
>example
>
>Column AgeRange has values such as 20-30, 40-50
>
>I'd like to do something like...
>
>select substr(agerange,1,instr(agerange,'-')-1) as StartAge,
>substr(agerange,instr(agerange,'-')+1) as EndAge
>
>I don't see an instr function supported in sqlite - any ideas?
>
>________________________________________________
>Get your own "800" number
>Voicemail, fax, email, and a lot more
>http://www.ureach.com/reg/tag
>
>  
>
Debra,

I think you will have to write your own user defined function to do
this. The SQL standard defines a POSITION() function that returns the
position of one string within another, but sqlite doesn't implement it.
It should be fairly simple to provide your own function to do this. You
can look at the standard functions in func.c for some examples of using
the custom function API routines (the same routines are used to define
all the standard functions; sum, round, substr, etc.).

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: Re: Instr, Locate or Splite

de f
In reply to this post by de f
Unfortunately, it's not always the same length (nor the same
chars) and the case statement could be slightly unwieldy.  I may
have to resort to that anyway.


Thanks


________________________________________________
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag


---- On Mon, 17 Oct 2005, Kurt Welgehausen ([hidden email])
wrote:

> If you're sure all the range strings are formatted
> correctly and if StartAge <= EndAge, you can do it
> with a case expression. The length of the range
> string will be >= 3 and <= 7; from the length you
> can deduce and hard code the indices in the substr
> calls.
>
> Regards
>
>