Parse data from notes field

  • I need to parse payment data from a notes field. Example of data below.  I tried the Len function but got mixed results, Any help or examples would be greatly appreciated.

    Total charges reported are $107.79.  Total Paid amount is $83.70.  Patient reponsibility is nothing ($0.00).

    Total charges reported are $1757.34.  Total Paid amount is $1537.67.  Patient reponsibility is nothing ($0.00

    Total charges reported are $707.11.  Total Paid amount is $707.11.  Patient reponsibility is nothing ($0.00).

    Thank you,

  • have a look at CharIndex

    /* from https://docs.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql */
    DECLARE @document VARCHAR(64);
    SELECT @document = 'Reflectors are vital safety' +
    ' components of your bicycle.';
    SELECT CHARINDEX('bicycle', @document);

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Tav29 wrote:

    I need to parse payment data from a notes field. Example of data below.  I tried the Len function but got mixed results, Any help or examples would be greatly appreciated.

    Total charges reported are $107.79.  Total Paid amount is $83.70.  Patient reponsibility is nothing ($0.00). Total charges reported are $1757.34.  Total Paid amount is $1537.67.  Patient reponsibility is nothing ($0.00 Total charges reported are $707.11.  Total Paid amount is $707.11.  Patient reponsibility is nothing ($0.00). Thank you,

    What results are you hoping to obtain, based on your examples?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thank you Johan,

    I tried the Charindex and I'm able to find the starting position. I have multiple positions 42,43,44,45,46.  What's the best way to go about this? a Case statement with the Len function?

    Thank you,

    Tav

     

  • Hi Phil,

    I'm trying to exact the Payment information (In Red). My problem is they all have different starting positions,  I did the Charindex recommend by Johan and I get 42,43,44,45,46 as starting positions. I just want the dollar value, I just don't know how to go about it without grabbing any text values.

    Total charges reported are $107.79.  Total Paid amount is $83.70.  Patient reponsibility is nothing ($0.00).

    Total charges reported are $1757.34.  Total Paid amount is $1537.67.  Patient reponsibility is nothing ($0.00).

    Total charges reported are $707.11.  Total Paid amount is $707.11.  Patient reponsibility is nothing ($0.00).

    Thanks for your help.

  • If the data really is as well structured as your examples suggest, the following should work. If not, something more refined will be required.

    DROP TABLE IF EXISTS #T1;

    CREATE TABLE #T1
    (
    Col1 VARCHAR(500) NOT NULL
    );

    INSERT #T1
    (
    Col1
    )
    VALUES
    ('Total charges reported are $107.79. Total Paid amount is $83.70. Patient reponsibility is nothing ($0.00).')
    ,('Total charges reported are $1757.34. Total Paid amount is $1537.67. Patient reponsibility is nothing ($0.00')
    ,('Total charges reported are $707.11. Total Paid amount is $707.11. Patient reponsibility is nothing ($0.00).');

    SELECT t.Col1
    ,TotalPaid = SUBSTRING(t.Col1, c.Pos1, c.Pos2 - c.Pos1)
    FROM #T1 t
    CROSS APPLY
    (
    SELECT Pos1 = CHARINDEX('Total Paid amount is $', t.Col1) + 22
    ,Pos2 = CHARINDEX('Patient reponsibility', t.Col1) - 3
    ) c;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks Phil,

    I'm sorry for such a novice question(New to SQL) I have this information in a Notes Field along with an ID Field in a table. How do I reference the note field and extract the payments and ID to the temp table?

    Thank you,

    Tav

     

  • Never mind Phil, I got it. Thank you for your help.  also thank you Jonah.

  • Phil,

    I'm getting this error. Here is my sql statement:

    SELECT Notes, NoteId

    ,TotalPaid = SUBSTRING(Notes, c.Pos1, c.Pos2 - c.Pos1)

    FROM dbo.NoteReceive

    CROSS APPLY

    (

    SELECT Pos1 = CHARINDEX('Total Paid amount is $', Notes) + 22

    ,Pos2 = CHARINDEX('Patient reponsibility', Notes) - 3

    WHERE (CategoryID = 9)

    ) c;

    Error:

    Msg 537, Level 16, State 2, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

     

  • Fixed. Absolute Value (ABS)

  • That means you have some data which does not follow the convention. You can avoid the error by filtering out the rows which do not match, like this:

    SELECT t.Col1
    ,TotalPaid = SUBSTRING(t.Col1, c.Pos1, c.Pos2 - c.Pos1)
    FROM #T1 t
    CROSS APPLY
    (
    SELECT Pos1 = CHARINDEX('Total Paid amount is $', t.Col1) + 22
    ,Pos2 = CHARINDEX('Patient reponsibility', t.Col1) - 3
    ) c
    WHERE c.Pos1 > 0 AND c.Pos2 > 0;

    Or you can track down the suspect rows, like this

    SELECT t.Col1
    FROM #T1 t
    CROSS APPLY
    (
    SELECT Pos1 = CHARINDEX('Total Paid amount is $', t.Col1) + 22
    ,Pos2 = CHARINDEX('Patient reponsibility', t.Col1) - 3
    ) c
    WHERE c.Pos1 = 0 OR c.Pos2 = 0;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hey Phil,

    You are correct had 30,000+ records with the word "Responsibility" misspelled and 400+ spelled correctly.  Ran replace function to correct the misspelling.

    SELECT Pos1 = CHARINDEX('Total Paid amount is $', t.Col1) + 22

    ,Pos2 = CHARINDEX('Patient reponsibility', t.Col1) - 3

    Thank you again for all your help, it's greatly appreciated. I'm sure I'll be back with more questions, just started to do SQL at my current job.  Have a great day.

     

    • This reply was modified 2 years, 8 months ago by  Tav29.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply