SQLTeam.com | Weblogs | Forums

INSTR with an Occurrence that needs to be a variable


#1

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


#2

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

#3

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


#4

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 :slightly_frowning_face:


#5

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


#6

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


#7

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 :pray:


#8

hi

i am working on it right now ..

:slight_smile:

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


#9

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

#10

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 :persevere:

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.


#11

or if you are using SQL Server 2016

string_split function


#12

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.


#13

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 :slight_smile:

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 :slight_smile:
Can somebody help me here :wink::-):wink:

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

image


#14

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.