CODEWITHSUNDEEP
sqlstringms-accesssubstring
Mcgroger
Mcgroger

Reputation: 67

Is there a function in ms-access sql-query with which one can extract a certain string?

I was wondering if there is a function in MS-Access SQL-Query with which one can extract a given string of each cell of a column (similar to substring() but with a string as criteria).

For example, I would like to extract the keywords SUN and FUN from the following column (named Lyrics)

Lyrics 

AeSUN9SL
Pla4FUNP
L2wqSUNop
ASUNfdNNK
7dssFUN9SL

so that I am only left with: Lyrics 

SUN
FUN
SUN
SUN
FUN

in the column. Since the length as well as the number of "unused" characters before and after the string keyword differ, I could not use left() and right().

Is there another way to solve this problem?

Upvotes: 0

Views: 236

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use case and like:

select (case when lyrics like '%SUN%' then 'SUN'
             when lyrics like '%FUN%' then 'FUN'
        end)

In MS Access (which was not the original tag):

select iif(lyrics like '*SUN*', 'SUN'
           iif(lyrics like '*FUN*', 'FUN', NULL)
          )

You can also include this in an UPDATE:

update t
    set lyrics = iif(lyrics like '*SUN*', 'SUN'
                     iif(lyrics like '*FUN*', 'FUN', NULL)
                    ) ;

Upvotes: 0

Deepika
Deepika

Reputation: 296

You can use regular expression subtring function https://www.techonthenet.com/oracle/functions/regexp_substr.php

select regexp_substr(Lyrics,'(SUN|FUN)', 1,1, 'i') from yourtablenamehere;

Upvotes: 1

Related Questions