Hi, Does anyone have any suggestions for this?
Chris
---------------------------------------
Hi Apology if this is the wrong forum, I am new to SQL and I would normally be doing this operation through Excel PowerQuery but its keeps crashing, so I thought I would give this a try.
So my rawdata comes from an SQL View, I use Excel to import this data and through Excel I can issue SQL commands/statements to manipulate what columns I want. I have limited SQL experience I can extract some detail but not to this level.
Step 1; my raw data, I am needing to extract all the 6 digit codes from the column called 'IN_CODE'.
| Step1 | | Codes are all 6 digits length, there also extra spaces between codes |
| PROJECT_ID | PROJECT_TITLE | IN_CODE |
| 1246 | NAME2 | R12345 and R45647, RA4564 & RB4561 |
| 1261 | NAME3 | R12478 |
| 1276 | NAME4 | R12457, |
| 1291 | NAME5 | RA4789 |
| 1306 | NAME6 | TY7894 |
| 1321 | NAME7 | FG5674 |
| 1336 | NAME8 | R45789 , TY7894 |
| 1351 | NAME9 | R13445 R78456 & R96325 (SCCA) R44557 (Dept) R77889 R11122 |
| 1366 | NAME10 | RT7845 |
| 1381 | NAME11 | RT1234 |
Step 2, I then want to remove all unwanted text, e.g. 'and' ',', '&' , '(SCCA)' etc There are also extra spaces just for a good measure.
| Step2 | | Codes are all 6 digits length, there also extra spaces between codes |
| PROJECT_ID | PROJECT_TITLE | IN_CODE |
| 1246 | NAME2 | R12345 R45647 RA4564 RB4561 |
| 1261 | NAME3 | R12478 |
| 1276 | NAME4 | R12457 |
| 1291 | NAME5 | RA4789 |
| 1306 | NAME6 | TY7894 |
| 1321 | NAME7 | FG5674 |
| 1336 | NAME8 | R45789 TY7894 |
| 1351 | NAME9 | R13445 R78456 R96325 R44557 R77889 R11122 |
| 1366 | NAME10 | RT7845 |
| 1381 | NAME11 | RT1234 |
Step 3, so I want to get the data from step 1 to look like this -
| Step3 | | |
| PROJECT_ID | PROJECT_TITLE | IN_CODE |
| 1246 | NAME2 | R12345 |
| 1246 | NAME2 | R45647 |
| 1246 | NAME2 | RA4564 |
| 1246 | NAME2 | RB4561 |
| 1261 | NAME3 | R12478 |
| 1276 | NAME4 | R12457 |
| 1291 | NAME5 | RA4789 |
| 1306 | NAME6 | TY7894 |
| 1321 | NAME7 | FG5674 |
| 1336 | NAME8 | R45789 |
| 1336 | NAME8 | TY7894 |
| 1351 | NAME9 | R13445 |
| 1351 | NAME9 | R78456 |
| 1351 | NAME9 | R96325 |
| 1351 | NAME9 | R44557 |
| 1351 | NAME9 | R77889 |
| 1351 | NAME9 | R11122 |
| 1366 | NAME10 | RT7845 |
| 1381 | NAME11 | RT1234 |
Any help appreciated.
Chris C
