Ask Your Question
0

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
0

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

Ratslinger gravatar image

Hello,

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

UPDATE "SBOD"
 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

Comments

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

Stats

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

Seen: 76 times

Last updated: May 27 '18