Convert Excel formula into SQL query-CodePudding

I am rather new to SQL but enthusiastic and willing to learn. I have am running an SQL query successfully so far however i am stuck on the below and hoping you can help, its a little too complex for me kowledge. I have an Excel formula:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("Sourced from internal",A1)-1),":",REPT(" ",100)),100))

This is extracting a text string from a large cell of data containing text, numbers, dates. It works perfectly in Excel however i'm lost where to start converting this to a working SQL function on a field named 'PRODSUMMARY' within my DB. Can anyone assist please?

I tried to experiment using a few similar posts however i am getting an error message.

Below is the data pulled into the PRODSUMMARY field, and what I achieve with the Excel:

PROACTIVE: SEV 1
INCIDENT START TIME: 01/17/2021 14:25:04
ID: urgent-abc12345-6789101f011
ALT ID: ABC-12345-000-F011
PRODUCT: NETWORK
EQUIPMENT TYPE: NETWORK DEVICE
INCIDENT TYPE: NETWORK ISSUE ABC-12345-000-F011: Network Loss SOURCED FROM INTERNAL MONITORING SYSTEM
MONITORING ALARM ID: ABCDE-1234BND56RMRMWRTE
TEXT: Device is inaccessible for monitoring

Expected result of formula: extract the incident type wording. For example in the above I would only want to extract "Network Loss" The constant would be "SOURCED FROM INTERNAL MONITORING SYSTEM" and the location of the colons.

I need to extract wording after the 2nd colon and before the word Sourced, the word/s will be variable lengths depending on the incident type being reported.

Kind regards

Benny

CodePudding user response：

You start by creatuoing a table and checking the manual for commands that correspond

CREATE TABLE mytable(mytext TEXT)

INSERT INTO mytable VALUES ("PROACTIVE: SEV 1
INCIDENT START TIME: 01/17/2021 14:25:04
ID: urgent-abc12345-6789101f011
ALT ID: ABC-12345-000-F011
PRODUCT: NETWORK
EQUIPMENT TYPE: NETWORK DEVICE
INCIDENT TYPE: NETWORK ISSUE ABC-12345-000-F011: Network Loss SOURCED FROM INTERNAL MONITORING SYSTEM
MONITORING ALARM ID: ABCDE-1234BND56RMRMWRTE
TEXT: Device is inaccessible for monitoring
")

SELECT
TRIM(
  RIGHT(
      REPLACE(
          LEFT(mytext,locate("Sourced from internal",mytext)-1)
      ,":",SPACE(100)
      ) 
,100)
) sometext
FROM mytable;

| sometext     |
| :----------- |
| Network Loss |

db<>fiddle here

CodePudding user response：

In other languages this could be done directly with a regular expression. In MySQL a regex will only get us so far.

Starting with

SELECT REGEXP_SUBSTR(PRODSUMMARY,'INCIDENT TYPE:.?:\s(.?)\sSOURCED')

We get INCIDENT TYPE: NETWORK ISSUE ABC-12345-000-F011: Network Loss SOURCED

From that we can use SUBSTRING_INDEX to get to the second colon and space:

SELECT SUBSTRING_INDEX(REGEXP_SUBSTR(`PRODSUMMARY`,'INCIDENT TYPE:.*?:\s*(.*?)\s*SOURCED'),': ',-1)

So now we have 'Network Loss SOURCED'

TRIM the trailing ' SOURCED'

SELECT trim(TRAILING ' SOURCED' FROM 
  substring_index(
     REGEXP_SUBSTR(`PRODSUMMARY`,'INCIDENT TYPE:.*?:\s*(.*?)\s*SOURCED'),': ',-1)) 
  as incidentText from `myTable`

Using a regular expression for the initial search allows for variable text to appear before the target items.