Well, it's pretty easy to add regex functionality, for example via PCRE. You can add this to your custom build of SQLite, or you can add it to your client application and register a regex function with SQLite after the database is opened, then use it in your queries. I can provide an example if you're interested in going this route.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeetWare Sent: Thursday, June 16, 2011 12:06 AM To: General Discussion of SQLite Database Subject: [sqlite] Breaking a string into two Hi all, I have some source data that includes a "Silo And Region" column of two words appended together, such as 'NorthPlains', 'SouthPlains', 'NorthSlopes', 'SouthSlopes' etc. I want to split them into two columns. How can I do this in SQLite? A regex or offset/position and replace function would take care of this (by looking for the second uppercase letter), but there's no regex or offset/position function provided. If I know all of the possible combinations beforehand, such as: create table "Silo Group" ( ID integer primary key not null , Name text collate nocase not null ) ; insert into "Silo Group" (Name) values ('North') ; insert into "Silo Group" (Name) values ('South') ; create table "Region" ( ID integer primary key not null , Name text collate nocase not null ) ; insert into "Region" (Name) values ('Plains') ; insert into "Region" (Name) values ('Slopes') ; then I can extract by looking for a match in each table, such as: select "Silo Group".Name as "Silo Group" , "Region".Name as "Region" from "Import" left join "Silo Group" on "Import"."Silo And Region" like "Silo Group".Name || '%' left join "Region" on "Import"."Silo And Region" like % || "Region".Name ; But I don't know all of the parts (ie "Silo Group" and "Region") until I bring in the Import. Any ideas? Hopefully I'm missing something obvious in SQLite. I am basically trying to normalise the supplied data, and don't want to have to resort to application code, just for the sake of this one function. Thanks, Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
