Get string between brackets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get string between brackets

Author

Topic

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2014-10-03 : 18:41:38

Hello,

I'm trying to run a SELECT statement to get two different values from a field that looks like this:

"Sample text [123], Sample text 2 [345]"

The two values I'm trying to grab has to be after the last comma. So in this case, I need to get

Value 1: 345
Value 2: Sample text 2

Is this possible to do? I can't create functions to accomplish this.

Thank you!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-05 : 15:28:15

Possible -> Yes
Recommended -> No

This really should be done in frontend program.

with cte (f)
  as (          select 'Sample text [123]'
      union all select 'Sample text [123], Sample text 2 [345]'
      union all select 'Sample text [123], Sample text 2 [345], Sample text 3 [567]'
     )
select f
      ,ltrim(rtrim(case when charindex('[',right('[],'+f,charindex(',',reverse('[],'+f))-1))=0
                      then right('[],'+f,charindex(',',reverse('[],'+f))-1)
                      else left(right('[],'+f,charindex(',',reverse('[],'+f))-1),charindex('[',right('[],'+f,charindex(',',reverse('[],'+f))-1))-1)
                   end
                  )
            ) as f1
      ,case when charindex('[',right('[],'+f,charindex(',',reverse('[],'+f))-1))=0
          then ''
          else ltrim(rtrim(replace(right(right('[],'+f,charindex(',',reverse('[],'+f))-1),charindex('[',reverse('[],'+f))-1),']','')))
       end as f2
  from cte

tkepongo
Yak Posting Veteran

62 Posts

Posted - 2014-10-06 : 11:36:38

quote:

Originally posted by bitsmed

Possible -> Yes
Recommended -> No

This really should be done in frontend program.

with cte (f)
  as (          select 'Sample text [123]'
      union all select 'Sample text [123], Sample text 2 [345]'
      union all select 'Sample text [123], Sample text 2 [345], Sample text 3 [567]'
     )
select f
      ,ltrim(rtrim(case when charindex('[',right('[],'+f,charindex(',',reverse('[],'+f))-1))=0
                      then right('[],'+f,charindex(',',reverse('[],'+f))-1)
                      else left(right('[],'+f,charindex(',',reverse('[],'+f))-1),charindex('[',right('[],'+f,charindex(',',reverse('[],'+f))-1))-1)
                   end
                  )
            ) as f1
      ,case when charindex('[',right('[],'+f,charindex(',',reverse('[],'+f))-1))=0
          then ''
          else ltrim(rtrim(replace(right(right('[],'+f,charindex(',',reverse('[],'+f))-1),charindex('[',reverse('[],'+f))-1),']','')))
       end as f2
  from cte

Wow, thank you for responding. I definitely agree this should not be recommended. I guess I'll just use a RIGHT function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources