SQL- Need numbers from a column for string

Question

I have a table like this.

MES_id        MES_for_col
4717       4717 = ( 4711 + 4712 + 4713)/ 3
4729       4729 = ( 4723 + 4724 + 4725 + 4726)/4 
4788       4788 = ( 4780 + 4781 + 4782 + 4783 + 4784 + 4785 )/6 
4795       4795 = ( 4793 + 4794 ) / 2

I need a query which will give an output like (only numbers before division sign from mes_for_col in column2 )

column1    column2
4717         4711
4717         4712
4717         4713
4729         4723
4729         4724
4729         4725
4729         4726
4788         4780...

and so on

CAn i use patindex here? Is there any query you can suggest? Thanks

score 2 · Accepted Answer · answered Mar 11 '15 at 12:44

Patindex only takes 2 parameters. If you want to search step by step, using CHARINDEX will work better since you can give it a starting position. However you can split using XML. You will need to filter out text before '=' and after '/', then replace all characters you don't want included with nothing.

Try this:

DECLARE @t table(MES_id int, MES_for_col varchar(max))
INSERT @t values
(4717, '4717 = ( 4711 + 4712 + 4713)/ 3'),
(4729, '4729 = ( 4723 + 4724 + 4725 + 4726)/4'),
(4788, '4788 = ( 4780 + 4781 + 4782 + 4783 + 4784 + 4785 )/6'),
(4795, '4795 = ( 4793 + 4794 ) / 2')

SELECT MES_id, t.c.value('.', 'VARCHAR(2000)') as column2
FROM (
    SELECT MES_id, x = CAST('<t>' + 
        REPLACE(REPLACE(REPLACE(REPLACE(STUFF(SUBSTRING(MES_for_col, 0,
        CHARINDEX('/', MES_for_col)), 1, CHARINDEX('=', MES_for_col), ''), 
        ' ', ''), ')', ''), '(', ''),  '+', '</t><t>') + '</t>' AS XML)
    FROM @t
) a
CROSS APPLY x.nodes('/t') t(c)

Result:

MES_id  column2
4717    4711
4717    4712
4717    4713
4729    4723
4729    4724
....

SQL- Need numbers from a column for string

1 Answers1