CODEWITHSUNDEEP
sqlitereplacewildcard
d0g
d0g

Reputation: 1389

How to do replace with wildcards in SQLite?

I'm trying to replace a bunch of strings in a SQLite database.

I want to remove CHECKCARD \d\d\d\d (in regex notation) from the beginning.

Does the replace for SQLite have this feature?

I.e., CHECKCARD 1215 AA BB CC should become AA BB CC

CHECKCARD 0715 DD EE should become DD EE

I tried

update ZUSERPAYEE set ZNAME = replace (ZNAME,"CHECKCARD ____ ","");

but that did not work.

I need it to be a one-line command ... so I can run from Terminal on Mac.

Upvotes: 0

Views: 3006

Answers (1)

Aaron Miller
Aaron Miller

Reputation: 3780

The SQLite substr() function should be your savior here:

UPDATE ZUSERPAYEE
SET ZNAME = substr(ZNAME, 16)
WHERE ZNAME LIKE "CHECKCARD %"

This will find any row where ZNAME begins with CHECKCARD, and replace the value of ZNAME with the substring from the sixteenth character (i.e. after CHECKCARD ####) to the end.

More on SQLite core functions, including a surprisingly featureful set of string handling functions.

Upvotes: 2

Related Questions