How to parse string in SQL Server [closed]

2fjabf4q 于 2023-10-15 发布在 SQL Server

关注(0)|答案(2)|浏览(135)

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;

But it did not get the desired result .

sql-server

来源：https://stackoverflow.com/questions/77245535/how-to-parse-string-in-sql-server

2条答案

按热度按时间

pcww981p1#

Please try the following solution based on tokenization instead of parsing.

Notable points:

1st CROSS APPLY removes parenthesis and everything in between.
2nd CROSS APPLY is tokenizing input as XML.
XQuery .query() method retrieves third word and everything after it.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, promo_name VARCHAR(1024));
INSERT @tbl (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');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT t.*
    , c.query('data(/root/r[position() ge 3])').value('text()[1]', 'VARCHAR(1024)') AS result
FROM @tbl AS t
CROSS APPLY (SELECT TRIM(STUFF(promo_name, 1, CHARINDEX(')', promo_name), ''))) AS t1(tokens)
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t2(c);

Output

ID	promo_name	result
1	(Web) promo addf 2000 from 4000, 2500 from 5000, 3000 from 6000	2000 from 4000, 2500 from 5000, 3000 from 6000
2	(CPA Partners) promo abc2a% 200 from 2000, 1000 from 2000	200 from 2000, 1000 from 2000
3	(CPA) promo sdafg1300 1300 from 6000	1300 from 6000

赞(0）回复(0）举报 2023-10-15

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.

Declare @string nvarchar(2000)

set @string = '"(CPA Partners) Promo ddb 500 from 1000, 1000 from 2000"'

select len('"(CPA Partners) Promo ddb 5')
select patindex('%[0-9]%', @string)

Select left (@string, patindex('%[0-9]%', @string)-1)
Select Right (@string, LEN(@STRING) - patindex('%[0-9]%', @string)+1 )

赞(0）回复(0）举报 2023-10-15

我来回答

How to parse string in SQL Server [closed]

2条答案

相关问题

热门标签

最新问答