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,
February 21, 2022 at 12:28 pm
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
February 21, 2022 at 12:45 pm
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
February 21, 2022 at 12:47 pm
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
February 21, 2022 at 12:54 pm
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
February 21, 2022 at 1:18 pm
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
February 21, 2022 at 1:39 pm
Never mind Phil, I got it. Thank you for your help. also thank you Jonah.
February 21, 2022 at 1:46 pm
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.
February 21, 2022 at 2:04 pm
Fixed. Absolute Value (ABS)
February 21, 2022 at 2:06 pm
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
February 22, 2022 at 12:18 am
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply