logo
down
shadow

get everything before a string including itself oracle


get everything before a string including itself oracle

Content Index :

get everything before a string including itself oracle
Tag : sql , By : Bin
Date : November 22 2020, 03:01 PM

around this issue The solution will have to work when the "token" ( the '81' in your example ) appears between two slashes, or right at the beginning of the string and before a slash, or right after the last slash at the end of the string. It should not match if '81' appears as part of a "token" (between slashes or before the first or after the last slash). Also, if the "token" appears more than once, it should be replaced (with everything before it) only once, and if it doesn't appear at all, then the original string should be unchanged.
If these are the rules, then you can do something like I show below. If any of the rules are different, the solution can be modified to accommodate.
with
  event_dynamic_attribute ( attribute_value ) as (
    select '28/29/81/732536/1496071' from dual union all
    select '29/33/530813/340042/88'  from dual union all
    select '81/6883/3902/81/993'     from dual union all
    select '123/45/6789/81'          from dual
  ),
  substitution ( token, replacement ) as (
    select '81', 'mathguy is great'  from dual
  )
select attribute_value,
       regexp_replace (attribute_value, '(^|.*?/)' || token || '(/|$)',
                                        replacement || '\2', 1, 1) new_attrib_value
from   event_dynamic_attribute cross join substitution
;

ATTRIBUTE_VALUE         NEW_ATTRIB_VALUE                       
----------------------- ----------------------------------------
28/29/81/732536/1496071 mathguy is great/732536/1496071         
29/33/530813/340042/88  29/33/530813/340042/88                  
81/6883/3902/81/993     mathguy is great/6883/3902/81/993       
123/45/6789/81          mathguy is great

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Oracle query String including hyphen character


Tag : sql , By : Robert M
Date : March 29 2020, 07:55 AM
With these it helps That's not my observation of how Oracle treats hyphens. Here's a brief sample of what I see:
SQL> select * from fb;

ID
----------
Web-Page
Web Page
Web -Page

SQL> select * from fb where lower(id) like '%-pa%';

ID
----------
Web-Page
Web -Page

Including a condition dynamically in sql(oracle)


Tag : sql , By : jgood
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I have a query as below , try:
select --------
from table a
left outer join ....c
where 
((a.column='123') and (c.column='456'))
or c.column is not NULL

ORACLE ORA00907: Differences in connection string using OraOLEDB.Oracle vs TNS lookup via Oracle in OraClient10g_home3


Tag : sql , By : Marc
Date : March 29 2020, 07:55 AM
hope this fix your issue OK I've work it out.
to all those that spent any time thinking about it.

Oracle row_number greater than and including 1


Tag : sql , By : Antony Briggs
Date : March 29 2020, 07:55 AM
With these it helps I have a query that uses the row_number function. I want to return all rows greater than 1 but also include 1. When I try , Use count instead of row_number.
select * from (select count()over(partition by .. order by) cn
                 from ...) x
where x.cn > 1
select * from (select count()over(partition by .. order by) cn,
                      row_number()over(partition by .. order by) rn
                 from ...) x
where x.cn > 1
create table mytab(id number, name varchar2(100));
insert into mytab values(1,'Ram');
insert into mytab values(1,'Rahim');
insert into mytab values(1,'Reuben');
insert into mytab values(2,'Esther');
insert into mytab values(3,'Veena');
insert into mytab values(3,'Visu');
insert into mytab values(4,'Vishnu');
commit;

select * from mytab;
ID NAME 
1 Ram 
1 Rahim 
1 Reuben 
2 Esther 
3 Veena 
3 Visu 
4 Vishnu
select * 
  from (select id, 
               name,
               count(1) over(partition by id) cn, 
               row_number() over(partition by id order by name) rn 
          from mytab) x
 where x.cn > 1;
ID  NAME    CN  RN
1   Rahim   3   1
1   Ram     3   2
1   Reuben  3   3
3   Veena   2   1
3   Visu    2   2

Oracle SQL for Last Business Day of Current Month including federal holidays in oracle


Tag : sql , By : jamerson
Date : March 29 2020, 07:55 AM
wish help you to fix your issue Store those federal holidays in a holidays table as DATE type and then try something like this: Find the oldest ( MAX ) day in the last seven days of the month that is neither a Saturday or a Sunday nor a holiday mentioned in the holidays table.
The assumptions here are that 1) not all seven days at the end of the month can all be holidays or weekend and 2) Saturday and Sundays are off. You may adjust the level or where clause accordingly, depending on whether the above assumption should always hold true or not.
SELECT MAX(dt) AS last_working_day 
FROM
(
SELECT last_day(SYSDATE) - level + 1 as dt
FROM dual CONNECT BY
     level <= 7  -- the last seven days of the month
     )  WHERE TO_CHAR(dt,'DY', 'NLS_DATE_LANGUAGE = AMERICAN') NOT IN ('SAT','SUN')
     AND dt NOT IN ( SELECT holiday from federal_holidays );
SELECT MAX(dt)
  FROM calendar
  WHERE isbusinessday = 'Y' 
AND TO_CHAR(dt,'YYYYMM') = TO_CHAR(SYSDATE,'YYYYMM');
Related Posts Related QUESTIONS :
  • SQL Server 2005 For XML Explicit - Need help formatting
  • How do I use T-SQL Group By
  • How do I split a string so I can access item x?
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • Pass data from ssrs subreport to parent
  • how to enter values in rowguid column?
  • Read multiple tables from dataset in Powershell
  • In SQL how to I exclude a record if there are more than 3 characters after a dash
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com