Re: [sqlite] Breaking a string into two

[BareFeetWare]

> BareFeetWare  wrote:
>> 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. 

On 16/06/2011, at 10:11 PM, Igor Tandetnik wrote:

> You could do something like
> 
> ltrim(substr("Silo And Region", 2), 'abc...xyz')
> 
> to extract the second part (spell out the rest of the alphabet in place of 
> '...'). The first part is left as an exercise for the reader.

Great advice, Igor, thank you. Works well.

I maintain that we really need some string searching ability, built into 
SQLite. It's a major omission that is often an issue.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
 --
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Breaking a string into two

[Igor Tandetnik]

BareFeetWare  wrote:
> 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. 

You could do something like

ltrim(substr("Silo And Region", 2), 'abc...xyz')

to extract the second part (spell out the rest of the alphabet in place of 
'...'). The first part is left as an exercise for the reader.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Breaking a string into two

[Simon Slavin]

On 16 Jun 2011, at 5:27am, BareFeetWare wrote:

> There could be dozens of each but the main issue is that I don't know what 
> they are until I get the data. That's what I meant by:
> 
>>> I don't know all of the parts (ie "Silo Group" and "Region") until I bring 
>>> in the Import.
> 
> So I can't completely populate lookup tables before I bring in the Import. 
> There will be a new Import periodically which might contain new Silo Groups 
> and Regions, and I won't be able to manually add those to the lookup tables 
> before importing.

Sorry.  It could be done by matching 'A', then matching 'B', etc. but the 
results would be horrible.  I recommend you do it in your programming language, 
which should be able to spot the first capital letter much more easily than 
SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Breaking a string into two

[Michael Stephenson]

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

Re: [sqlite] Breaking a string into two

[BareFeetWare]

Hi Simon,

Thanks for the reply.

> On 16 Jun 2011, at 5:05am, BareFeetWare wrote:
> 
>> 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.

On 16/06/2011, at 2:19 PM, Simon Slavin wrote:

> How many different first words are there ?  Is it just 'North' and 'South' ?  
> Maybe the two other directions too ?  Or are there hundreds of them ?

There could be dozens of each but the main issue is that I don't know what they 
are until I get the data. That's what I meant by:

>> I don't know all of the parts (ie "Silo Group" and "Region") until I bring 
>> in the Import.

So I can't completely populate lookup tables before I bring in the Import. 
There will be a new Import periodically which might contain new Silo Groups and 
Regions, and I won't be able to manually add those to the lookup tables before 
importing.

Ideas?

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

Re: [sqlite] Breaking a string into two

[Simon Slavin]

On 16 Jun 2011, at 5:05am, BareFeetWare wrote:

> 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 many different first words are there ?  Is it just 'North' and 'South' ?  
Maybe the two other directions too ?  Or are there hundreds of them ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Breaking a string into two

[BareFeetWare]

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