CODEWITHSUNDEEP
regexsqlite
user2923339
user2923339

Reputation: 159

SQLite: How to split a column

I have a column containing two names, which I'd like to extract into two separate columns surname1 and surname2 (I don't need the name nor the initial letter (e.g. N.)).

The exemplary content of that column is:

AwyeEaef2012 MS101 N.Lopez-O.Lorenzi.txt

-Lopez and Lorenzi are these two which we are looking for in this row.

What is good about my situation is that the first name comes always after the first dot (.) and ends just before the dash (-) and the second name comes just after second dot and ends just before the third dot and txt (.txt).

I know how to write a regex and using LIKE check if that column contains some specific surname but not the opposite way- how to read surnames and write them into two new columns.

Several rows from that column look like below: 

WyeEaef MN2014 MS401 N.Lopez-O.Lorenzi.txt
AwyufEQ WCH2014 OS401 N.Lorenzi-O.Lopez.txt
THAFa5u WCH2014 LS107 N.Larry-O.Lolly.txt

So the pattern is as I mentioned *.Name1-[A-Z].Name2.txt

Where * is max 30 characters of capital and small letters and numbers

It could be approached in this manner: other words we need to divide this into substrings divided by dots first substring is a waste, the second without two last characters(a dash and acapital letter, e.g. -O) is the first name, the third substring is the second name and the fourth is another waste(a former file format).

I'd like to have an output of three columns: initialColumn, firstName, secondName

Upvotes: 1

Views: 869

Answers (1)

user2923339
user2923339

Reputation: 159

The workaround that I wrote as a formula in Excel which I personally don't love, but might be useful for someone in the future.

=MID(A1;FIND(".";A1;1)+1;FIND(".";A1;FIND(".";A1;1)+1)-FIND(".";A1;1)-3)

I was surprised that Excel can manage processing ~0.5mln of records in the blink of an eye.

Upvotes: 1

Related Questions