CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2007
prider
prider

Reputation: 23

Select a value using iif access query

I have a row who contains this value :

account :
adm.ahrgrst001
adm.ns2dhdujhd
adm.ff2hdjhh
adm.haidhidh103
adm.hshiksh122
adm.cn3ehuioe

i want to extract two different values: when it ends like adm.hshiksh122 i want to extract hshiksh and with start with adm.cn3ehuioe i want ehuioe both without the adm. at the beginning

I have thinked this

IIF(isnumeric(RIGHT (account,3)),LEFT(account,LEN(account)-4),RIGHT (account,LEN(account)-7))

the value that are like adm.cn3ehuioe i got wrong like adm.cn3ehui and adm.ahrgrst001 is correct ahrgrst

Thanks to everyone who will read

Upvotes: 1

Views: 309

Answers (1)

forpas
forpas

Reputation: 164154

The correct way to get the 2 types of values is with:

account LIKE 'adm.[!0-9][!0-9]#[!0-9]*'

for the values that have 2 letters, 1 digit and letters after the dot, and:

account LIKE 'adm.*###'

for the values that end in 3 digits.

So use this:

SELECT IIF(
  account LIKE 'adm.*###' ,
  MID(account, 5, LEN(account) - 7),
  MID(account, 8)
) AS result
FROM tablename
WHERE account LIKE 'adm.*###' OR account LIKE 'adm.[!0-9][!0-9]#[!0-9]*'

If there are no other values than these 2 types then you may remove the WHERE clause.

Results for your sample data:

result
ahrgrst
dhdujhd
hdjhh
haidhidh
hshiksh
ehuioe

Upvotes: 1

Related Questions