CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Don Carnage
Don Carnage

Reputation: 71

How to get expression from string

Here is the string :'(a+b)+(x/y)*1000' from that string i want to get '(x/y)' meaning i want the part that contains the division to check later if denominator <> 0 to avoid division by zero.

The string formula can vary but divisions are always between parenthesis.

How can i achieve that in sql ?

Upvotes: 1

Views: 174

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Remove everything up to the second ( using stuff and get the characters to the next ) using left.

declare @S varchar(20)
set @S = '(1+2)+(3/4)*1000'

select left(S2.S, charindex(')', S2.S)-1)
from (select stuff(@S, 1, charindex('(', @S), '')) as S1(S)
  cross apply (select stuff(S1.S, 1, charindex('(', S1.S), '')) as S2(S)

Upvotes: 1

MatBailie
MatBailie

Reputation: 86715

Bits that it appears you already have (based on a comment you made)...

  • Pos of the '/' = CHARINDEX('/', yourString)
  • Pos of the ')' = CHARINDEX(')', yourString, CHARINDEX('/', yourString) + 1)

The position of the ( is a little different, as you need to search backwards. So you need to reverse the string. And so you also need to change the starting position.

  • CHARINDEX('(', REVERSE(yourString), LEN(yourString) - CHARINDEX('/', yourString) + 2)

Which give the position from the right hand side. LEN(yourString) - position + 1 give the position from the left hand side.

Add that all together and you get a very long formula...

SUBSTRING(
  yourString,
    LEN(yourString)
  - CHARINDEX('(', REVERSE(yourString), LEN(yourString) - CHARINDEX('/', yourString) + 2)
  + 1,
    CHARINDEX(')', yourString, CHARINDEX('/', yourString) + 1)
  - LEN(yourString)
  + CHARINDEX('(', REVERSE(yourString), LEN(yourString) - CHARINDEX('/', yourString) + 2)
  - 1
)

Upvotes: 1

Related Questions