Assistance with SQL statement

%3CLINGO-SUB%20id%3D%22lingo-sub-1210124%22%20slang%3D%22en-US%22%3EAssistance%20with%20SQL%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1210124%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Does%20anyone%20have%20any%20suggestions%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3CP%3E---------------------------------------%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%20Apology%20if%20this%20is%20the%20wrong%20forum%2C%20I%20am%20new%20to%20SQL%20and%20I%20would%20normally%20be%20doing%20this%20operation%20through%20Excel%20PowerQuery%20but%20its%20keeps%20crashing%2C%20so%20I%20thought%20I%20would%20give%20this%20a%20try.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20my%20rawdata%20comes%20from%20an%20SQL%20View%2C%20I%20use%20Excel%20to%20import%20this%20data%20and%20through%20Excel%20I%20can%20issue%20SQL%20commands%2Fstatements%20to%20manipulate%20what%20columns%20I%20want.%26nbsp%3B%20I%20have%20limited%20SQL%20experience%20I%20can%20extract%20some%20detail%20but%20not%20to%20this%20level.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3B%20my%20raw%20data%2C%20I%20am%20needing%20to%20extract%20all%20the%206%20digit%20codes%20from%20the%20column%20called%20'%3CSTRONG%3EIN_CODE%3C%2FSTRONG%3E'.%20%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22654%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2257%22%3EStep1%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2257%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2257%22%3ECodes%20are%20all%20%3CSTRONG%3E6%20digits%3C%2FSTRONG%3E%20length%2C%20there%20also%20extra%20spaces%20between%20codes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E%3CSTRONG%3EPROJECT_ID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3E%3CSTRONG%3EPROJECT_TITLE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3E%3CSTRONG%3EIN_CODE%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1246%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME2%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER12345%20and%20%26nbsp%3B%26nbsp%3B%20R45647%2C%20RA4564%20%26amp%3B%20RB4561%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1261%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME3%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER12478%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1276%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME4%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER12457%3CSTRONG%3E%2C%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1291%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME5%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ERA4789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1306%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME6%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1321%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME7%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3EFG5674%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1336%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME8%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ER45789%20%3CSTRONG%3E%2C%3C%2FSTRONG%3E%20%26nbsp%3B%20TY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2257%22%3E1351%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2257%22%3ENAME9%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2257%22%3ER13445%20R78456%20%26amp%3B%20R96325%20(SCCA)%20R44557%20(Dept)%20R77889%20R11122%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1366%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME10%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ERT7845%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22101.62%22%20height%3D%2230%22%3E1381%3C%2FTD%3E%3CTD%20width%3D%22125.28%22%20height%3D%2230%22%3ENAME11%3C%2FTD%3E%3CTD%20width%3D%22426.1%22%20height%3D%2230%22%3ERT1234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%202%2C%20I%20then%20want%20to%20remove%20all%20unwanted%20text%2C%20e.g.%20'%3CSTRONG%3Eand%3C%2FSTRONG%3E'%20'%3CSTRONG%3E%2C%3C%2FSTRONG%3E'%2C%20'%3CSTRONG%3E%26amp%3B'%3C%2FSTRONG%3E%20%2C%26nbsp%3B%3CSPAN%3E%3CSTRONG%3E%20'(SCCA)'%20%3C%2FSTRONG%3Eetc%26nbsp%3B%20There%20are%20also%20extra%20spaces%20just%20for%20a%20good%20measure.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%20width%3D%22654%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2286%22%3EStep2%3C%2FTD%3E%3CTD%20width%3D%22162%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22406%22%3ECodes%20are%20all%206%20digits%20length%2C%20there%20also%20extra%20spaces%20between%20codes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPROJECT_ID%3C%2FTD%3E%3CTD%3EPROJECT_TITLE%3C%2FTD%3E%3CTD%3EIN_CODE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ER12345%20R45647%20%26nbsp%3B%26nbsp%3B%20RA4564%20RB4561%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1261%3C%2FTD%3E%3CTD%3ENAME3%3C%2FTD%3E%3CTD%3ER12478%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1276%3C%2FTD%3E%3CTD%3ENAME4%3C%2FTD%3E%3CTD%3ER12457%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1291%3C%2FTD%3E%3CTD%3ENAME5%3C%2FTD%3E%3CTD%3ERA4789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1306%3C%2FTD%3E%3CTD%3ENAME6%3C%2FTD%3E%3CTD%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1321%3C%2FTD%3E%3CTD%3ENAME7%3C%2FTD%3E%3CTD%3EFG5674%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1336%3C%2FTD%3E%3CTD%3ENAME8%3C%2FTD%3E%3CTD%3ER45789%20TY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER13445%20R78456%20R96325%20R44557%20R77889%20R11122%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1366%3C%2FTD%3E%3CTD%3ENAME10%3C%2FTD%3E%3CTD%3ERT7845%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1381%3C%2FTD%3E%3CTD%3ENAME11%3C%2FTD%3E%3CTD%3ERT1234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%203%2C%20so%20I%20want%20to%20get%20the%20data%20from%20step%201%20to%20look%20like%20this%20-%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22414%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2286%22%3EStep3%3C%2FTD%3E%3CTD%20width%3D%22162%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EPROJECT_ID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EPROJECT_TITLE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EIN_CODE%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ER12345%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ER45647%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ERA4564%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1246%3C%2FTD%3E%3CTD%3ENAME2%3C%2FTD%3E%3CTD%3ERB4561%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1261%3C%2FTD%3E%3CTD%3ENAME3%3C%2FTD%3E%3CTD%3ER12478%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1276%3C%2FTD%3E%3CTD%3ENAME4%3C%2FTD%3E%3CTD%3ER12457%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1291%3C%2FTD%3E%3CTD%3ENAME5%3C%2FTD%3E%3CTD%3ERA4789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1306%3C%2FTD%3E%3CTD%3ENAME6%3C%2FTD%3E%3CTD%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1321%3C%2FTD%3E%3CTD%3ENAME7%3C%2FTD%3E%3CTD%3EFG5674%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1336%3C%2FTD%3E%3CTD%3ENAME8%3C%2FTD%3E%3CTD%3ER45789%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1336%3C%2FTD%3E%3CTD%3ENAME8%3C%2FTD%3E%3CTD%3ETY7894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER13445%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER78456%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER96325%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER44557%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER77889%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3ENAME9%3C%2FTD%3E%3CTD%3ER11122%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1366%3C%2FTD%3E%3CTD%3ENAME10%3C%2FTD%3E%3CTD%3ERT7845%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1381%3C%2FTD%3E%3CTD%3ENAME11%3C%2FTD%3E%3CTD%3ERT1234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%20C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1210124%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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_IDPROJECT_TITLEIN_CODE
1246NAME2R12345 and    R45647, RA4564 & RB4561
1261NAME3R12478
1276NAME4R12457,
1291NAME5RA4789
1306NAME6TY7894
1321NAME7FG5674
1336NAME8R45789 ,   TY7894
1351NAME9R13445 R78456 & R96325 (SCCA) R44557 (Dept) R77889 R11122
1366NAME10RT7845
1381NAME11RT1234

 

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_IDPROJECT_TITLEIN_CODE
1246NAME2R12345 R45647    RA4564 RB4561
1261NAME3R12478
1276NAME4R12457
1291NAME5RA4789
1306NAME6TY7894
1321NAME7FG5674
1336NAME8R45789 TY7894
1351NAME9R13445 R78456 R96325 R44557 R77889 R11122
1366NAME10RT7845
1381NAME11RT1234

 

Step 3, so I want to get the data from step 1 to look like this - 

Step3  
PROJECT_IDPROJECT_TITLEIN_CODE
1246NAME2R12345
1246NAME2R45647
1246NAME2RA4564
1246NAME2RB4561
1261NAME3R12478
1276NAME4R12457
1291NAME5RA4789
1306NAME6TY7894
1321NAME7FG5674
1336NAME8R45789
1336NAME8TY7894
1351NAME9R13445
1351NAME9R78456
1351NAME9R96325
1351NAME9R44557
1351NAME9R77889
1351NAME9R11122
1366NAME10RT7845
1381NAME11RT1234

 

Any help appreciated.

 

Chris C

0 Replies