Hi there, I've been bashing my brains out trying to figure out how to remove text/numbers between the ( and ) in a column within my sqlite3 db. I've tried rtrim, ltrim, substr() all to no avail. Any expert sqlite3 guru care to help me? I'd appreciate it. Thanks.
A:
It might partially depend on your SQLite implementation. From the docs:
The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator.
If your implementation does have a REGEXP defined, then it should be as simple as a regex of "(.*?)\(.*?\)(.*)", returning only groups 1 and 2 to ignore what's inside the parentheses.
This approach is not portable because possibly most SQLite implementations do not have REGEXP defined.
Someone at this forum recently went through a long thread asking for almost the same thing, and he didn't have any luck. To the best of my knowledge, there's no built-in functionality to do this.
His final answer in there was to trim off a constant number of characters, because all the data was uniformly ending in '@xyz.com' -- if your data is similarly uniform, you could just trim off the leading and trailing X and Y characters.
In fact, back in 2003, someone made a patch for charindex support, but for some reason it apparently was never added into the trunk. Someone else made a similar patch with more functionality, but this requires you to load an external shared library -- again, not exactly portable.
That last one might be your best option -- writing a shared library in C, and LOADing it from SQLite. Or you could just implement the string modification in whichever language you're using SQLite :)
Mark Rushakoff
2009-08-31 01:39:40