extract text from the string

Question

I need to extract text from the string KWR/50X50X5/1.4301 between /, or 50x50x5 in T-SQL. I've tried using Substing, however, does not go to me. Ultimately, I need to add the values (sum values) in between / without character x (for example, 50 + 50 + 5 = 105) I would be grateful for your help.

For reference: http://stackoverflow.com/questions/27010295/split-alpha-and-numeric-using-sql — Sorrel Vesper, Oct 29 '15 at 08:35
Can be there `50X50X5X4X7`? Can you give all possible patterns? — Giorgi Nakeuri, Oct 29 '15 at 08:36
It can be just as written . The principle is similar for all cases. So there may be more figures to sum , and between them x . Thanks for your response — AdiT, Oct 29 '15 at 08:44
for example, PWPQ/80X20/1.4301 ,PWO/120/1.4404, PWOI/120x9x90x80/1.4404 — AdiT, Oct 29 '15 at 08:49
you means all this data in one column?? if in different column then check this..http://sqlfiddle.com/#!3/e1fae/1 — anwaar_hell, Oct 29 '15 at 08:53

anwaar_hell · Answer 1 · 2015-10-29T08:54:48.660

0

select substring(firstpart,1,CHARINDEX('/',firstpart)-1)
from 
(select 
substring(pattern,
                 CHARINDEX('/',pattern)+1,
                 datalength(pattern))  as firstpart
from tessst
)X;

edited Oct 29 '15 at 08:54

answered Oct 29 '15 at 08:47

anwaar_hell

756
5
23

great, I would need more sum of the numbers without the character x – AdiT Oct 29 '15 at 09:02
@AdiT do you want only numbers and not X?? eg 120x9x90x80 for this you want 120 9 90 80?? – anwaar_hell Oct 29 '15 at 09:04
I want the sum of these numbers you have entered . thanks – AdiT Oct 29 '15 at 09:10

score 0 · Accepted Answer · answered Oct 29 '15 at 09:03

Try this:

DECLARE @t TABLE (id INT,  v VARCHAR(100) )

INSERT  INTO @t
VALUES  ( 1, 'PWPQ/80X20/1.4301' ) ,
        ( 2, 'PWO/120/1.4404' ),
        ( 3, 'PWOI/120X9X90X80/1.4404' )


;WITH cte1 AS(SELECT id, SUBSTRING(v, 
                                   CHARINDEX('/', v) + 1, 
                                   CHARINDEX('/', v, CHARINDEX('/', v) + 1) - CHARINDEX('/', v) - 1) AS v
             FROM @t),
      cte2 AS(SELECT id, CAST ('<X>' + REPLACE(v, 'X', '</X><X>') + '</X>' AS XML) AS v FROM  cte1)
SELECT id, SUM(Split.a.value('.', 'int')) AS v  
FROM  cte2 a CROSS APPLY v.nodes ('/X') AS Split(a)
GROUP BY id

Output:

First cte is for extracting value between /. Second cte for casting those values to xml format. The last statement is standard trick for transposing string with delimeter to separate rows.

great, did not mention that I have a table in which the specified text (code ) . I would like to do a select from table and point the id of the record and the query will return me the value of that figured out . Sory for the confusion — AdiT, Oct 29 '15 at 09:30

extract text from the string

2 Answers2