How to find the nearest character? (Example Now Included)

Database
Enthusiast

How to find the nearest character? (Example Now Included)

Hi,

 I have to split up large SQL statements out of the log tables, these are text strings that are often > 20,000 characters long. To do so, I must assure I don't split the text string in the middle single quotes, which are often part of SQL statements. So I want some SQL to find where the single quotes are at. More specifically -

 

Here are the requirements: In a very long text string SQL statement which may have data encased in single quotes and often has multiple single quotes, I want to find the character location of nearest even numbered single quote to a fixed number (such as 5,000). So for example: in a long text string over 5,000 characters, how can I find where or at what position, the nearest even numbered single quote is at, where the word nearest in this case is less than 5,000.

 

AS AN EXAMPLE - If this is the SQL String:

 

SELECT f_nm, l_nm, gender, dob, c_pref, acct_size, c_status, last_tx_dt from TBL_PERSON where l_nm IN (‘happy’, ‘sleepy’, ‘grumpy’, ‘silly’) and gender = ‘M’ and c_pref IN (‘top’, ‘top-middle’, ‘exec’); 

 

In this example we can not parse or break any of the single quoted data: ‘happy’, ‘sleepy’, ‘grumpy’, ‘silly’, ‘M’, ‘top’, ‘top-middle’, ‘exec’. 

In this example, the string is small, just so I can provide an example - If the set or fixed value was say 115 which is in the middle of the word ‘sleepy’, then the NEAREST even numbered single quote is at position 109. It would be safe to parse the string after position 109 and the quotes and data within the quotes would be preserved.

 

In this example, I just picked an arbitrary number like 115. But the HELP I NEED ---  how to find the character location of nearest even numbered single quote in a string. Even numbered because it assures I won't break up or parse the SQL statement in the middle of a quote. I am thinking we'll be using 5,000 for the fixed value. 

 

Thanks in Advance!!!

 

 

  • (E
1 REPLY
Junior Supporter

Re: How to find the nearest character?

if you can throw a representative example, someone will help for sure