# Text Extract Code Help **URL:** \ **Category:** Q&A\ **Tags:** error, calculations, dataset, Business-Intelligence-Engineer, quick-sight\ **Created:** [September 17, 2024, 4:28pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231 "2024-09-17T16:28:20Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 17, 2024, 4:28pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/1 "2024-09-17T16:28:20Z")
substring(um.notes, charindex(‘#$#’, um.notes) + 4, charindex(‘$#$’, um.notes) - charindex(‘#$#’, um.notes)) The above code pulls the bolded information from the below entry. Documentation Delay - ]0;3 (For Data mining purpose only) #$# **Waited 64 minutes for paperwork. $#$** (JAH). I am only trying to pull the information bolded text below. Documentation Delay - ]0;3 (For Data mining purpose only) #$# _ **Waited 64 minutes for paperwork.** _ $#$ (JAH). I have tried this to try and cut the extraction by 3 character places. substring(um.notes, charindex(‘#$#’, um.notes) + 3, charindex(‘$#$.’, um.notes) - charindex(‘#$#’, um.notes) - 3) However, this causes the error below: ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/d/e/de38891f6064e2ec664f238525054fa3222ad205.png) ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 17, 2024, 4:55pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/2 "2024-09-17T16:55:19Z")
Hello @andyhob, welcome to the Quick Sight community! I think the 3rd part of your substring function is just barely off. If you try changing it slightly and add some parenthesis for order of operations, you should be able to resolve it: `substring(um.notes, charindex(‘#$#’, um.notes) + 4, (charindex(‘$#$’, um.notes) - 1) - (charindex(‘#$#’, um.notes) + 4))` Please let me know if that helps! ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 17, 2024, 5:26pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/3 "2024-09-17T17:26:34Z")
> [@andyhob](#): > > Documentation Delay - ]0;3 (For Data mining purpose only) #$# **Waited 64 minutes for paperwork. $#$** (JAH). I tried your suggestion with the below in bold being displayed: Documentation Delay - ]0;3 (For Data mining purpose only) #$# **Waited 64 minutes for paperwork. $#$ (JA**H). ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 17, 2024, 5:55pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/4 "2024-09-17T17:55:08Z")
Hello @andyhob, what database are you querying data from? I am looking at some SQL Server documentation for the [charindex function](https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql?view=sql-server-ver16) you are using, and it looks like we may be able to just use the starting position parameter to try and fix this. Maybe try this instead of my previous suggestion: `substring(um.notes, charindex(‘#$#’, um.notes, 4), (charindex(‘$#$’, um.notes, 1) - 1) - charindex(‘#$#’, um.notes, 4))` It seems like we need to specific to start at the $ for the `$#$` portion. I think this should give us a better result. If you are still having issues can you run each charindex function separately to see what values are being returned? Provide me with the full string length, `charindex(‘$#$’, um.notes, 1)`, and `charindex(‘#$#’, um.notes, 4)`. Then maybe we can find an alternative solution if this doesn’t work. ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 18, 2024, 12:34pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/5 "2024-09-18T12:34:25Z")
Your first suggestion caused the same error as before. When I tried charindex(‘$#$’, notes,1) it generated the error below. ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/b/1/b14c611b9e14262cb4fd758fb91ede92cd649831.png) ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 18, 2024, 2:56pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/6 "2024-09-18T14:56:28Z")
Hello @andyhob, I just noticed in your error code it mentions Redshift, so identifying the starting position is not an option with that version of charindex(). > **[CHARINDEX function - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_CHARINDEX.html)** > > Returns the location of the specified substring within a string. In that case, we need to piece the function together to try and get the expected result. It also seems like the starting point of the substring isn’t our issue, but rather the number of characters in the string. I went ahead and counted and we need to find a way for this to return the number 32. If you just run 2 separate calculations: 1. `charindex(‘#$#’, um.notes) + 4` 2. `charindex(‘$#$’, um.notes) - 1` 3. `len(um.notes)` What values are you getting? ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 18, 2024, 3:18pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/7 "2024-09-18T15:18:00Z")
These are the values I got: 1. 351 2. 383 3. 393 ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 18, 2024, 3:22pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/8 "2024-09-18T15:22:28Z")
Hello @andyhob, okay, well that seems to be working as expected. So then if you run this: `(charindex(‘$#$’, um.notes) - 1) - (charindex(‘#$#’, um.notes) + 4)` Are you not getting the result of 32? ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 18, 2024, 3:33pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/9 "2024-09-18T15:33:58Z")
I am getting 32 when I run this. ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 18, 2024, 6:14pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/10 "2024-09-18T18:14:55Z")
Hello @andyhob, if that is the case, does this work or is it still throwing an error? `substring(um.notes, charindex(‘#$#’, um.notes, 4), (charindex(‘$#$’, um.notes) - 1) - (charindex(‘#$#’, um.notes) + 4))` If the last part is returning 32, this should work unless we are hitting some weird Redshift limitation. Let me know what happens. ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 18, 2024, 6:34pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/11 "2024-09-18T18:34:24Z")
New error with that one. ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/9/4/94f5e4be589196421680c24f59245e1697c44812.png) ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 18, 2024, 9:54pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/12 "2024-09-18T21:54:14Z")
Hello @andyhob, sorry, I used to the wrong version of the first charindex function. Try this instead: `substring(um.notes, charindex(‘#$#’, um.notes) + 4, (charindex(‘$#$’, um.notes) - 1) - (charindex(‘#$#’, um.notes) + 4))` That should resolve the issue from the last error. ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 19, 2024, 2:12pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/13 "2024-09-19T14:12:09Z")
Hi @WLS-DM, I tried the last recommendation and received this error. It seems we’re getting a different variation of the same error each time. When we run the substring all together is coming up with a negative length, but running the individual pieces of the substring, they are showing the correct character length. ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/d/6/d6515c7a1525565d3c7e366b183165ac2cc3a0da.png) ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 19, 2024, 2:51pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/14 "2024-09-19T14:51:38Z")
Hello @andyhob, I know in the example returned value, you mentioned this: `Documentation Delay - ]0;3 (For Data mining purpose only) #$# Waited 64 minutes for paperwork. $#$ (JAH).` I am assuming, this is not the only value that is being returned from the `um.notes` field, am I correct? If that is the case, is there a scenario where the value isn’t formatted with the `#$#` before the `$#$` in the string? If those values are ever switched, then this substring calculation would not work. That is the only thing I can think of as an issue here because all of our testing shows the current format should return the expected value. Something else you can try is building this function as a calculated field rather than in SQL to see if there is a similar error. That would look like this: `substring({notes}, locate({notes}, '#$#')+4, (locate({notes}, ‘$#$’) - 1) - (locate({notes}, ‘#$#’) + 4))` I am curious if the result from the Quick Sight calculated field will be any different than the one from Redshift directly. ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 19, 2024, 5:45pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/15 "2024-09-19T17:45:21Z")
> [@WLS-DM](#): > > substring({notes}, locate({notes}, ‘#$#’)+4, (locate({notes}, ‘$#$’) - 1) - (locate({notes}, ‘#$#’) + 4)) Ok, so it seems to work as a calculated field. I can see it pulling the correct information in the QS preview pain. However, when going down this path the refresh fails, and the dataset will not refresh with this calculated field. ---
**Author:** ![WLS-DM](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-dm/32/36109_2.png) [@WLS-DM](https://community.amazonquick.com/u/WLS-DM)\ **Post date:** [September 19, 2024, 5:56pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/16 "2024-09-19T17:56:55Z")
Hey @andyhob, what if you build it on the analysis instead of on the dataset directly? It seems like we are hitting a Redshift limitation, so maybe we can bypass it that way. ---
**Author:** ![andyhob](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@andyhob](https://community.amazonquick.com/u/andyhob)\ **Post date:** [September 19, 2024, 6:13pm UTC](https://community.amazonquick.com/t/text-extract-code-help/35231/17 "2024-09-19T18:13:07Z")
Well that worked!!! Thanks for all your help with this!