CODEWITHSUNDEEP
sqlsql-servert-sqlsubstringcharindex
marko
marko

Reputation: 517

sql - getting text between parentheses

I have text like this:

San Demetrio Corone (CS)
Villanova Tulo (NU)

I need to get the text between parentheses, I did this

SELECT SUBSTRING(a.place, CHARINDEX('(', a.place) + 1, CHARINDEX(')', a.place, CHARINDEX('(', a.place)+ 1)  - CHARINDEX(')', a.place) - 1)
FROM tab1 a

but I'm getting error about lenght parameter, what's wrong in that?

Upvotes: 1

Views: 2489

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Your length input to SUBSTRING is off. The length should be the difference in position between the closing and opening parentheses, offset by one less.

SELECT
    place,
    SUBSTRING(place,
              CHARINDEX('(', place) + 1,
              CHARINDEX(')', place) - CHARINDEX('(', place) - 1) AS abbr
FROM tab1;

screen capture from demo link below

Demo

Upvotes: 3

Related Questions