INSTR with an Occurrence that needs to be a variable

seaside_escape · October 9, 2018, 7:54pm

I need to pick out some string from a string that varies in length.
The end result is to pick out the id number, contained in brackets, from a string.
An example of the data is:
Jones (4)
Smith (Deceased) (100)
The Result from the above example would need to be:
4
100

Ideally I want a query that will find the start position of the last open bracket and pick up the data from that point onwards, because the id number, contained in brackets, is always at the end.

INSTR(contact_data,'(',1,2) gives me the start point, but requires me to provide the nth occurrence - which is a variable because some data contains 1 bracket, some contain more.

I have tried to introduce a variable into occurrences, but it errors - please see below and please help if you can!

SELECT * FROM OPENQUERY(PROCLAIM,'
SELECT a.contact_data,
INSTR(a.contact_data,''('',1,a.bracket_occurrences) AS searching_for_start_point,
SUBSTRING(a.contact_data,LOCATE(''('',a.contact_data,1)+1,LENGTH(a.contact_data)-LOCATE(''('',a.contact_data,1)-1) AS contact_id
	FROM(
		SELECT contact_data,
		CAST(LENGTH(contact_data) - LENGTH(REPLACE(contact_data,''('','''')) AS INT) AS bracket_occurrences
		FROM PUB.contacts
	) AS a
')

Unfortunately, I only have access to a very limited set of SQL commands as I am using Progress OpenEdge.

Below is a partial link to the functions I have access to if you want to have a look: documentation.progress .com /output /OpenEdge117 /openedge117 /#page /dmsrf%2Fopenedge-sql-functions.html%23

yosiasz · October 9, 2018, 8:15pm

there has got to be a better way, but like all things dealing with this type of stripping out values from strings,
it will come to bite you eventually.


create table #escape(shimsham varchar(50) )

insert into #escape
select 'Jones (4)' union
select 'Smith (Deceased) (100)'

;with cte
as
(
select shimsham , 
       REVERSE(shimsham) as shamshim, 
   substring(REVERSE(shimsham),charindex(')',REVERSE(shimsham),0) + 1, len(shimsham)) extract_numero,
	   charindex(')',REVERSE(shimsham),0) where_you_is
        
  From #escape
)

select reverse(substring(extract_numero,0,  charindex('(',extract_numero,0)))
 from cte
drop table #escape

mannesravya · October 10, 2018, 12:15am

drop table words
create table words
(
terms char(50)
)

insert into words values('Jones (4)')
insert into words values('Smith (Deceased) (100)')
insert into words values('pooja (right) (wrong) (1000)')

select substring(Terms,patindex('%[0-9]%',Terms),len(Terms)) from
(select replace(replace(terms,substring(terms,PATINDEX('%[,~,@,#,,%,&,*,(,.,!,_]%',terms),1),' '),substring(terms,PATINDEX('%[,~,@,#,,%,&,*,),.,!,_]%',terms),1),' ') as Terms
from words) a

seaside_escape · October 10, 2018, 8:40am

Hi yosiasz. Thank you for your suggestion but both REVERSE and CHARINDEX are not a recognised OpenEdge SQL Functions so they don't work - please refer to the website link I provided in my post so you can see the unfortunate limitations I have

seaside_escape · October 10, 2018, 8:50am

Hi mannesravya. Unfortunately, PATINDEX is not a recognised OpenEdge SQL function so it doesn't work in the query. The below are all I have available to use:
ABS, ACOS, ADD_MONTHS, ASCII, ASIN, ATAN, ATAN2, AVG, CASE, CAST, CDC_get_changed_columns, CDC_is_column_changed, CEILING, CHAR, CHR, COALESCE, CONCAT, CONVERT (ODBC compatible), CONVERT (Progress extension), COS, COUNT, CURDATE, CURTIME, CURRVAL, DATABASE, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, DB_NAME, DECODE, DEGREES, EXP, FLOOR, GREATEST, HOUR, IFNULL, INITCAP, INSERT, INSTR, LAST_DAY, LCASE, LEAST, LEFT, LENGTH, LOCATE, LOG10, LOWER, LPAD, LTRIM, MAX, MIN, MINUTE, MOD, MONTH, MONTHNAME, MONTHS_BETWEEN, NEXT_DAY, NEXTVAL, NOW, NULLIF, NVL, PI, POWER, PREFIX, PRO_ARR_DESCAPE function, PRO_ARR_ESCAPE function, PRO_ELEMENT function, QUARTER, RADIANS, RAND, REPEAT, REPLACE, RIGHT, ROUND, ROWID, RPAD, RTRIM, SECOND, SIGN, SIN, SQRT, SUBSTR, SUBSTRING (ODBC compatible), SUFFIX, SUM, SYSDATE, SYSTIME, SYSTIMESTAMP, TAN, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIME, TO_TIMESTAMP, TRANSLATE, UCASE, UPPER, USER, WEEK, YEAR

Wim_Leys · October 10, 2018, 3:17pm

Maybe you will get better responses on an OpenEdge forum. This is a SQL Server forum.

seaside_escape · October 11, 2018, 8:13am

Wim_Leys, It's still SQL at the end of the day, it just doesn't include all of the functions/commands. I was hoping some very clever person would work out a way of combining a few functions/commands to help me get my data...... I'm still hopeful

harishgg1 · October 11, 2018, 8:22am

hi

i am working on it right now ..

Hopefully I can give you what you are looking for ..

harishgg1 · October 11, 2018, 8:27am

One Way is to create a
Function
User Defined Function

and use it ..

is that okay ????

CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

seaside_escape · October 11, 2018, 8:46am

Hi harishgg1, Thank you so much for your answer. I'm sorry to say I don't have permission to write functions in the source

After Wim_Leys suggested posting my problem elsewhere, I posted it on "stackoverflow", and the good news is that I have had some possible solutions put forward......

SELECT
  contact_data, 
  rtrim( 
    ltrim( 
      lower( contact_data ), 
      'abcdefghijlmnopqrstuvwxyz( )' 
    ), 
    ')'
  )
from pub.contacts

Although this could trip me up if there are additional non-ID numbers in the string.

The other alternative is to guess at how many possible number of instances of brackets there could be - but there shouldn't be a lot to be honest......

select 
  contact_data, 
  rtrim(
    substring (
      contact_data,
      greatest( 
        instr( contact_data, '(', 1, 1 ), 
        instr( contact_data, '(', 1, 2 ), 
        instr( contact_data, '(', 1, 3 ) 
      ) + 1
    ), 
    ')'
  )
from pub.contacts

I think I'm going to use the last solution utilising GREATEST and maybe add in a few more instances just to make sure I'm covered!

Thank you so much for your time on this, I really appreciate it.

harishgg1 · October 11, 2018, 9:13am

or if you are using SQL Server 2016

string_split function

AndyC · October 11, 2018, 11:20am

The classic solution to a problem like this is regular expressions. Some SQL dialects have native support for them, others (like SQL Server) can be extended using mechanisms like the CLR to add the capability.

harishgg1 · October 14, 2018, 12:50pm

hi

I am trying to use Tally Table to give solution

Please other people see where I am and please take it further to help seaside

Create Data

use tempdb 
go 

drop table #data 
go 

create table #data 
(
data varchar(100)
)
go

insert into #data select 'Jones (4)'
insert into #data select 'Smith (Deceased) (100)'
go

I tried to find start position of the last open bracket (
using tally table

SQL

; WITH tally_cte 
     AS (SELECT N=1 
         UNION ALL 
         SELECT n + 1 
         FROM   tally_cte 
         WHERE  n + 1 < 20), 
     lastpostcte 
     AS (SELECT data, 
                Max(n) AS ' LAST ( position ' 
         FROM   tally_cte, 
                #data b 
         WHERE  Substring(b.data, n, 1) = '(' 
         GROUP  BY Substring(b.data, n, 1), 
                   data)
SELECT * 
FROM   lastpostcte

Result

Now I have added finding last ) position

Find Other Last ) also SQL

; WITH tally_cte 
     AS (SELECT N=1 
         UNION ALL 
         SELECT n + 1 
         FROM   tally_cte 
         WHERE  n + 1 < 20), 
     lastpostcte 
     AS (SELECT data, 
                Max(n) AS ' LAST ( position ' 
         FROM   tally_cte, 
                #data b 
         WHERE  Substring(b.data, n, 1) = '(' 
         GROUP  BY Substring(b.data, n, 1), 
                   data), 
     lastpostcteotherchar 
     AS (SELECT data, 
                Max(n) AS ' LAST ) position ' 
         FROM   tally_cte, 
                #data b 
         WHERE  Substring(b.data, n, 1) = ')' 
         GROUP  BY Substring(b.data, n, 1), 
                   data) 
SELECT * 
FROM   lastpostcteotherchar 

go

Results OTHER last ) character

Now I have to cut the string based on the above
Can somebody help me here :-)

OK I was able to do it
Seaside please check if OK

SQL

; WITH tally_cte 
     AS (SELECT N=1 
         UNION ALL 
         SELECT n + 1 
         FROM   tally_cte 
         WHERE  n + 1 < 20), 
     lastpostcte 
     AS (SELECT data, 
                Replace(Substring(data, Max(n) + 1, Len(data)), ')', '') AS Last
         FROM   tally_cte, 
                #data b 
         WHERE  Substring(data, n, 1) = '(' 
         GROUP  BY data) 
SELECT * 
FROM   lastpostcte;

Result

seaside_escape · October 15, 2018, 8:54am

Hi [harishgg1]

I'm afraid your solution using TALLY Table isn't working either. Your solution works fine in normal SQL, but when I use it through an OPENQUERY to an OpenEdge SQL Source, it doesn't work - and unhelpfully, I don't get an error code....

Msg 7399, Level 16, State 1, Line 6
The OLE DB provider "MSDASQL" for linked server "PROCLAIM" reported an error. The provider did not give any information about the error.
Msg 7321, Level 16, State 2, Line 6
An error occurred while preparing the query for execution against OLE DB provider "MSDASQL" for linked server "PROCLAIM".

But don't worry, I am happy to use the solution using GREATEST and INSTR.