Closed. This question needs details or clarity . It is not currently accepting answers.
Want to improve this question? Add details and clarify the problem by editing this post .
Closed 7 days ago.
Improve this question
I have this data ( fiddle ):
CREATE TABLE dbo.promocode
(
promo_name varchar(100)
);
INSERT dbo.promocode (promo_name)
VALUES ('(Web) promo addf 2000 from 4000, 2500 from 5000, 3000 from 6000'),
('(CPA Partners) promo abc2a% 200 from 2000, 1000 from 2000'),
('(CPA) promo sdafg1300 1300 from 6000');
I need to remove the first 3 "constructions" from those strings.
- constructions1 - everything inside parens
()
(could be any number of words) - constructions2 - word1
- constructions3 - word2 (may contain numbers,
%
, etc.)
I am trying to get these results:
2000 from 4000, 2500 from 5000, 3000 from 6000
200 from 2000, 1000 from 2000
1300 from 6000
I tried the following expression:
SELECT
SUBSTRING(Promo_name, CHARINDEX(' ', Promo_name,
CHARINDEX(' ', Promo_name, CHARINDEX(' ', Promo_name) + 1) + 1) + 1, LEN(Promo_name)) AS result
FROM
dbo.promocode;
2条答案
按热度按时间pcww981p1#
Please try the following solution based on tokenization instead of parsing.
Notable points:
CROSS APPLY
removes parenthesis and everything in between.CROSS APPLY
is tokenizing input as XML..query()
method retrieves third word and everything after it.SQL
Output
7bsow1i62#
If we are sure we will always look for first numeric digit and we need to take everything from that, then we can look for a numeric digit and take rest of the string.