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
[email protected]
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
