Ask Your Question

Need help with substring (or LOCATE) in BASE

asked 2018-05-27 09:58:28 +0100

brhanson2 gravatar image

updated 2018-05-27 10:19:37 +0100

I have a database called "SBOD" In it there is a field called "Book" Sample data in that field is "Bowyer-Small-Plain-Normal (KQWGBPD)"

It will always contain a skill (in this case Bowyer) preceding the first dash. I want to copy the data out of book up to the dash (In this example "Bowyer") and put in in my field called "Skill" in the same database.

Can someone give me the full UPDATE statement required to do this?

Thank you! Brad

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-05-27 21:31:15 +0100

Ratslinger gravatar image


Providing you always have the - in the Book filed, this will work:

 SET "Skill" = (SELECT "Skill" FROM (SELECT ID, RTRIM( SUBSTR( "Book", 1, LOCATE( '-', "Book" ) - 1 ) ) AS "Skill" FROM "SBOD")B WHERE B.ID = SBOD.ID)

This also used ID as the key in this table. Replace as needed (used three times).

In future be clear with proper terminology. SBOD is a table within the database and NOT the database. A database can contain many tables. Fields are within tables and not databases.

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more


Im new to this and appreciate the update on terminology. Im a retired RPG Programmer :)

Your solution was flawless, copy/paste/run and data is perfect.

Thank you!

brhanson2 gravatar imagebrhanson2 ( 2018-05-28 07:38:43 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-27 09:58:28 +0100

Seen: 76 times

Last updated: May 27 '18