Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to find the last word in a string in SQLITE

Posted on 2016-10-29
Medium Priority
Last Modified: 2016-11-01
I have an SQLite database with a Buyers_table which has a column:
The value has been entered as John Smith, John Q. Smith, John and Betty Smith, etc.
...basically many different "name strings" but with the last_name always the last word, with a preceding " " blank space.

I need to pull out just the lastName, the last word in the string.  I can do this successfully  ONLY WHEN the field is simply two words such as John Smith.
such as:

SELECT substr(, instr(, ' ') + 1) AS last_name
FROM Buyer;

Can anyone help me see how to extract just the last word, the last_name?  Yes I can do this in SQL, but this project, database, is SQLite so things like CHARINDEX() cannot be used.

Thanks so much,

Dan K
Question by:Dan Kaufman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
LVL 49

Expert Comment

ID: 41865529
work with a derived table usig a case expression, e.g.

      case when pos1 > 0 then substr(name, pos1 + 1)
           else name
from (
      SELECT instr(, ' ') pos1, name
      FROM Buyer

you can then branch according to the pos1 value returned by the derived table.

Author Comment

by:Dan Kaufman
ID: 41865848
Thank you excellent suggestion to use a derived table...I was stuck on the path of "simple" string manipulation.

However, it does appear to me at least that I'll need multiple nested loops to break down the reality of the "name" field, the reality of which is the folks entering the data to the Buyer_table can and do get creative on what a person's name is.  An example of the data I'm dealing with:
John Smith.
John G. Smith,
John and Betty Smith,
John G. and Betty Smith,
Mr. and Mrs. John G. Smith

I'm dealing with "names" with 1 space, 2 spaces, 3 spaces, 4 spaces, and even 5 spaces.   But all I need is the last word in the string.

which is why I have been trying to "simply" identify either the first space from the right instead of the left, and/or the last word in the "name string".

My initial query works just fine as long as the "name" is simply a first_name and a last_name.  It gives me very similar results to your derived table routine.  This basically just splits the string at the first, from the left, space.  However this does not directly give me the position number of the first space as your derived table routine does.

substr(, 1, instr(, ' ') - 1) AS first_name,
substr(,    instr(, ' ') + 1) AS last_name

The short of it is I'm not sure I am even capable of writing a branched-nested-derived table CASE expression that will deal with names having 3, 4, or even 5 spaces in them.  So...I'm hoping to find a "simple solution" that identifies either the last space from the left OR first space from the right, from which I can pull the last word in the string.

Thanks again!

Expert Comment

ID: 41866247
Hi Dan,

Maybe you can try REVERSE function

Reverse(substr(reverse(, 1 ,   instr(reverse(, ' ') ) ) AS last_name

Or in combination with len( - instr(reverse(, ' ') To get last space position.

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

LVL 49

Expert Comment

ID: 41866278
If reverse existed in SQLite it would work nicely.You can create your own functions in SQLite but I don't know which ones are good or bad as I have never really used SQLite in anger.

A more generic approach without a reverse function would be to use a technique derived from "splitstring" approaches. Dynamically build-up a "tally table" (a table of integers) using CTEs, then expand the data using a cross join, and through that find the maximum value of a space in that string. GROUP BY reduces the data back to single rows, and we can now locate the last name using that max(position of space in original string).

nb the example below creates 100 rows in tally, if you need more see note at base
Digits AS (
          SELECT 0 AS digit UNION ALL 
, Tally AS (
          SELECT tens.digit * 10 + ones.digit AS number
          FROM Digits ones
          CROSS JOIN Digits tens
        substr(name, 1, pos1 - 1) AS first_name
      , substr(name, pos1 + 1)    AS last_name
              , MAX(tally.number) AS pos1
        FROM buyer
        CROSS JOIN tally
        WHERE substr(, tally.number, 1) = ' '
        GROUP BY
     ) AS d
ORDER BY last_name, first_name

Open in new window

|           first_name | last_name |
| Mr. and Mrs. John G. |     Smith |
|              John G. |    Smith, |
|    John G. and Betty |    Smith, |
|       John and Betty |    Smith, |
|                 John |    Smith. |

[SQL Fiddle][1]

    ("name" TEXT(100))

     ('John Smith.')
    ,('John G. Smith,')
    ,('John and Betty Smith,')
    ,('John G. and Betty Smith,')
    ,('Mr. and Mrs. John G. Smith')

Open in new window


-- this is sql server syntax, but hopefully it conveys the required message
            + [10s].digit * 10
            + [100s].digit * 100  /* add more like this as needed */
            AS num
      FROM cteDigits [1s]
      CROSS JOIN cteDigits [10s]
      CROSS JOIN cteDigits [100s] /* add more like this as needed */

Open in new window


Assisted Solution

Jimy earned 1000 total points
ID: 41866496
This remove all characters from right, up to first space and then take this first part from whole string, so there will left only last name

select replace(, rtrim(, '1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM!@#$%^&*()_+-=`~[]\/{}|;:,.<>?'), '') AS last_name
FROM Buyer;

Open in new window


Author Comment

by:Dan Kaufman
ID: 41866907
Many thanks! to PortletPaul and Jimy, and Expert Exchange "at large", for your insight and expert help.

Jimy's solution, essentially "one line of code" was the simplest and most elegant for my purposes. Though I didn't mention it in my Question, the initial need and use of the solution "to pull the last word from a text string" was in an ORDER BY clause of the Buyer_table VIEW.  As I pointed out in my question, the folks entering the data to the Buyer Name field would do so in many creative ways...but as I think is most common we tend to think of a list of names as alphabetized by the person's last name.  This is why I was so frustrated that I couldn't write what seemed (in my little pea-brain) like simple code for a common need.  But Experts Exchange to the rescue!
LVL 32

Accepted Solution

awking00 earned 1000 total points
ID: 41867191
select substr(name,length(rtrim(lower(name),'abcdefghijklmnopqrstuvwxyz'))+1)
from buyer;

Author Comment

by:Dan Kaufman
ID: 41867289
Excellent awking00, ..."There's more than one way to skin a cat."

The only edit or addition I can see to be made would be to add the "-" dash character to the rtrim character list.  This will cover, include, those last_names that are hyphenated, such as Fitz-Patrick.

SELECT substr(name,length(rtrim(lower(name),'abcdefghijklmnopqrstuvwxyz-'))+1) AS last_name
from Buyer;

Thanks so much.  I've learned a lot.

Author Closing Comment

by:Dan Kaufman
ID: 41867308
Having a "Name" field/column in a database seems almost as common as a sunny day.  And having that Name added to the database_table as John Smith, John G. Smith, John and Betty Smith, etc. seems almost as common as a cloudy day.   AND...on a practical basis in using the Name_list, it would seem to me  that having the list in alphabetic order by a person's last_name would be pretty common too.

However for me at least it became a challenge with my feeble SQLite skills writing a simple ORDER BY clause to have my BUYERS VIEW in A-to-Z order by the Buyer's last names. The range of solutions was surprising to me too.  In the end though Albert Einstein was proven right again: “Make things as simple as possible, but not simpler.”

Thanks again first and foremost to Expert Exchange and in particular awking00 and Jimy and PortletPaul for your expert help.  I couldn't have done without you.
LVL 32

Expert Comment

ID: 41867383
To be sure, having names like your examples in one field is not very safe. I actually like to use five fields to store name, qualifier (e.g. Mr. Mrs, etc.), firstname, middle, lastname, extension (e.g.Jr., III, etc.). It prevents the issue of having names like Otto von Bismarck or Jill St. John retrieve false information.

Author Comment

by:Dan Kaufman
ID: 41867454
Excellent point awking00.

I didn't design the original "intake form" with the singular name field, but I will have considerable influence in improving it.  I can see by your two additional name examples that even "our" solution above would have problems since it is essentially keyed on the first-space-from-the-right to define what is the "last name".  We don't have too many von Bismarks in the US Southeast so it might not be a problem, but it'd be an interesting conumdrum to code around vs. eliminating the problem to begin with separate intake fields.

Keep up the good work!
LVL 49

Expert Comment

ID: 41867473
Mc. Donald .....

Any Scottish descendents in the Southeast?
LVL 32

Expert Comment

ID: 41868874
There may not be too many von Bismarcks, but I know there are a bunch of van Burens in Georgia :-)

Author Comment

by:Dan Kaufman
ID: 41869191
Awright...PortletPaul and awking00,

You got me there.  If any Mc. Donalds or van Burens show up as Buyers I'll whip up a CASE loop just for them.  Until then I'll keep pushing for a rewrite of the intake form, name_table and whatever else trouble y'all got me in.

Keep up the good work!

Featured Post

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question