We help IT Professionals succeed at work.

Access query to extract data

mlaurin
mlaurin asked
on
238 Views
Last Modified: 2012-05-07
Hi!  I need to extract from the WBS to the  next "_".  Examples below:

R_CS00185736_WBS-4.046.4_CN-AA2416
R_20_WBS-4.046.2_CN-AA400
R_67_WBS-4.047.3_CN-AA400
R_CUT_CS00163192_ECP_WBS-1.67A.1_CN-AA2158_ASIT_PAYLOADS_01283
R_CS00218685_WBS-4.046.4_CN-AA12638A
R_ZZ29275284_WBS-4.68G.12.1_CN-AA1-4
R_1_ZZ31368892_IA652Z4301-1_WBS-4.68G.13.1_CN-AA1
R_IC632Z9300-1_WBS-4.68G.10_CN-AA1-1
R_ZZ23847198_IC786Z0401-2_WBS-4.68G.11_CN-AA1
R_1_IC642Z1100-201,-203_WBS-3.68A.2_CN-AA1-28.3-1
R_622Z3060-X_ZZ30368294_WBS-4.68G.12.1_CN-AA1-1
R_1_ZZ32575861_654Z1295-6_WBS-3.68A.2_CN-AA1_45.2-1
R_ZZ31353531_WBS-4.68G.12.1_CN-AA1-3
R_654Z1295-2_ZZ32575874_WBS-3.68A.2_CN-AA1_45.2-1
R_622Z5060-1_ZZ30368292_WBS-4.68G.12.1_CN-AA1-1
R_IR780Z0402-4_ZZ31948808_WBS-3.68A.2_CN-AA1-37.1
R_788Z0417-1_-2_ZZ31948806_WBS-3.68A.2_CN-AA1-37.1
R_788Z0417-3_-4_ZZ31948814_WBS-3.68A.2_CN-AA1-37.1
R_627Z3200-2_ZZ31948903_WBS-3.68A.2_CN-AA1_40.1
Comment
Watch Question

Commented:
Something like this:
SELECT iif(instr(field1, "WBS-") <> 0, left(right(field1,  len(field1) - instr(field1, "WBS-") -3), instr(right(field1,  len(field1) - instr(field1, "WBS-") -3), "_")-1), "") as newcolumn
FROM Table1;

Open in new window

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Wow!  Perfect, thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.