select number between certain words in sql

sql substring between two special characters
substring sql
a sql query to select a string between two known strings
charindex sql
sql server substring between delimiter
sql, select text between brackets
sql get string between parentheses
how to select first 3 characters in sql

I have a question regarding extracting numeric information from text in SQL. The "shop_priceChange" sheet looks like this:

shop_id    apply_content
10048586    {"priceChange":"item price change from 88.0 to 98.0"}
10050114    {"priceChange":package price change from 0.8 to 1.0"}
10036306    {"priceChange":"item price change from 19.0 to 21.0"}

I hope to get below table by extracting before/after price information from "shop_priceChange" sheet:

shop_id    category    before    after
10048586    item        88        98
10050114    package     0.8       1.0
10036306    item       19.0       21.0

Thanks in advance for your help!


Not sure what DBMS you are using, but assuming a string pattern of "priceChange":"XXX price change from YYY to ZZZ", you can try something like this on Postgres:

SELECT shop_id,
  SUBSTRING(apply_content, 'priceChange":"(\w+)') category,
  SUBSTRING(apply_content, 'price change from ([0-9][\.\d]*) to') price_before,
  SUBSTRING(apply_content, 'price change from.*to ([0-9][\.\d]*)') price_after
FROM my_table
;

SQL Fiddle

How do I extract all data in between two characters of a string in SQL, Below is the query.. DECLARE @String NVARCHAR(max) ='aaaaa( bb (asdads) bbb )' SELECT LEFT(@String, CHARINDEX('(', @String)-1) +''� The SQL BETWEEN Operator. The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.


I'll share regexp_substr from mysql on dealing this requirement.

select shop_id, case when apply_content like '%item%' then 'item'
    when apply_content like '%package%' then 'package' else '' end as category    
    , regexp_substr(apply_content, '([0-9]+)[^0-9]+([0-9]+)') as `before`
    , replace(regexp_substr(
        regexp_substr(apply_content, '([0-9]+)[^0-9]+([0-9]+).+'), 'to ([0-9]+)[^0-9]+([0-9]+)') 
        , 'to ', '') as `after`
from test

Here's the dbfiddle

Sql Server: Query to get string between two characters or symbols , Or we can say getting text from string wrapped between two same or different characters. the requirement to get substring from within two specific characters or symbols. SQL SERVER: Generate Row Number/Serial Number with each row. Config (6) Window Functions (1) Word Counter (1) XML (15) xml data type in� select 'This is the first line' union all. select 'Second line' union all. select 'Word' union all. select 'This is the fourth line'. select case when charindex(' ', words, charindex(' ', words


I am not sure which DBMS you are using, I have proposed my solution using SQL Srever

DB FIDDLE

Use SUBSTRING and CHARINDEX

SELECT shop_id,SUBSTRING(apply_content, CHARINDEX(':"',apply_content)+2, CHARINDEX('price',apply_content, CHARINDEX('price',apply_content)+1) - CHARINDEX(':"',apply_content)-2) as Categoty
  ,SUBSTRING(apply_content, CHARINDEX('from',apply_content) + 4, CHARINDEX('to',apply_content) - CHARINDEX('from',apply_content) - 4) as [Before]
  ,SUBSTRING(apply_content, CHARINDEX('to',apply_content), CHARINDEX('"}',apply_content) - CHARINDEX('to',apply_content)) as [After]
FROM tbl

Extracting substring between two characters OR Delimeters using , Transact-SQL I am trying to extract everything between PipeSymbol(|) and select SUBSTRING(loginname,CHARINDEX('|',loginname)+1� The problem is that the second part of your substring argument is including the first index. You need to subtract the first index from your second index to make this work. SELECT SUBSTRING(@Text, CHARINDEX('the dog', @Text), CHARINDEX('immediately',@text) - CHARINDEX('the dog', @Text) + Len('immediately'))


WITH    SEAN_H_TEST_DATA    AS

(

SELECT  1 AS  primary_key ,    10048586  AS  shop_id     ,    '{"priceChange":"item price change from 88.0 to 98.0"}'  AS  apply_content   UNION
SELECT  2,   10050114   ,     '{"priceChange":package price change from 0.8 to 1.0"}'     UNION
SELECT  3,  10036306   ,    '{"priceChange":"item price change from 19.0 to 21.0"}'

)



SELECT primary_key ,  shop_id  ,   apply_content     ,

REGEXP_SUBSTR(apply_content ,  'item|package'  )   AS  category    ,
REGEXP_SUBSTR(apply_content  ,   '[[:digit:]]+.0|0.[[:digit:]]'  )   AS   before      ,
REGEXP_REPLACE(REGEXP_SUBSTR(apply_content  ,   '( to )([[:digit:]]+.0|0.[[:digit:]])'    ) ,  ' to ', '')   AS   after 

FROM   SEAN_H_TEST_DATA

ORDER BY 1

SQL Substring function overview, For few demos, the Adventureworks2016 database is used and for some other, SELECT FirstName, substring(firstname,1,5), lastname FROM Person. with the word 'on' using the SQL SUBSTRING function in the where clause. Using the SQL Server Substring function, the numeric sub-set of a string� Suppose it is required to extract the text written between '/'. Then the query will be as: --Query to extract the desired code. SELECT SUBSTRING(Code,CHARINDEX('/',Code)+1, ( ( (LEN(Code))-CHARINDEX('/', REVERSE(Code)))- CHARINDEX('/',Code))) AS Result FROM #tb. Result.


Assuming your database is Postgres, you can convert your text column to jsonb like this:

select shop_id
       , split_part(apply_content::jsonb->>'priceChange', ' '::text, 1) as category
       , split_part(apply_content::jsonb->>'priceChange', ' '::text, 5) as before
       , split_part(apply_content::jsonb->>'priceChange', ' '::text, 7) as after 
from shop_priceChange;

SQL BETWEEN Operator, The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: Demo Database. Below is a selection from the "Products" table in the Northwind sample database: � SELECT SUBSTRING_INDEX('Cats,Dogs,Rabbits', ',', 2); Result: Cats,Dogs. In this example, we select everything before the second comma. This is done by using a comma (,) as the delimiter, and 2 as the count. Example 2 – Select Everything to the Right. To select everything after a certain character, you need to use a negative value:


MySQL SUBSTRING() Function, SELECT SUBSTRING("SQL Tutorial", 5, 3) AS ExtractString; Can be both a positive or negative number. If it is a positive The number of characters to extract. Here is a SQL Server example which takes a string variable and spearates it into inidividual words. We use a combination of CHARINDEX, LEFT and RIGHT functions to achieve it. DECLARE @StringValue VARCHAR (200) = 'This is a string of words i want to separate'; WITH SeparateWords ( StringValue, Word, Position, RestOfLine) AS ( SELECT @StringValue , CASE CHARINDEX (' ',@StringValue) WHEN 0 THEN @StringValue ELSE LEFT (@StringValue, CHARINDEX (' ',@StringValue) -1) END , 1 , CASE


How to get a substring between two characters? – SQLServerCentral, Have a go at that, and post back if you have any specific problems or questions. RETURN. (. SELECT. SUBSTRING(@String, FirstIndexOfChar +1 It will help SQL Server to see this function as Deterministic, which will potentially lead to this function calls on another function that counts the number of� It the following SQL statement we are returning all Persons whose title is Mr. SELECT FirstName, LastName FROM Person.Person WHERE Title = 'Mr.' Other examples of equality conditions include: Quantity = 30; UnitPrice = .25; City = ‘El Paso’ You may have noticed that numbers are written as is, however, the text is enclosed in single quotes.


SQL Server SUBSTRING Function By Practical Examples, In this tutorial, you will learn how to use the SQL Server SUBSTRING() function to is a positive integer that specifies the number of characters of the substring to be returned. SELECT SUBSTRING('SQL Server SUBSTRING', 5, 6) result; SELECT DISTINCT � WHERE � NULL � AND � OR � IN � BETWEEN � LIKE � Column � SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL