CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Valter Silva
Valter Silva

Reputation: 16656

SQL Server: How to use substring in a query?

i have this kind of row:

[Arturo Ochando]  <20>

But i want only:

Arturo Ochando

How can i do that ?

And how use this in a 'select' operation ?

update: i would like to find the first and the last '[' ']' and catch only what is inside there.

Example:

voice: English version) [Cobalt Claw]

return

Cobalt Claw

Best regards, Valter Henrique.

Upvotes: 1

Views: 8505

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Get text between first [ and next ].

-- cte for test data
;with actor_character(character) AS
(
  select 'voice: English version) [Cobalt Claw]' union all
  select 'voice: English version) [Cobalt Claw' union all
  select 'voice: English version)  Cobalt Claw]' union all
  select 'voice: English version) ]Cobalt Claw[' union all
  select 'voice: English version)  Cobalt Claw'
)
select *,
  case
    -- Test for not valid positions
    when Start.Pos = 1 or Stop.Pos = 0
    then character
    else substring(character, Start.Pos, Stop.Pos-Start.Pos)
  end
from actor_character
  cross apply (select charindex('[', character)+1) as Start(Pos)
  cross apply (select charindex(']', character, Start.Pos)) as Stop(Pos)

Get text between first [ and last ].

-- cte for test data
;with actor_character(character) AS
(
  select 'voice: English version) [Cobalt Claw]' union all
  select 'voice: English version) [Cobalt Claw' union all
  select 'voice: English version)  Cobalt Claw]' union all
  select 'voice: English version) ]Cobalt Claw[' union all
  select 'voice: English version) [Cobalt]Claw]' union all
  select 'voice: English version)  Cobalt Claw'
)
select *,
  case
    -- Test for not valid positions
    when Start.Pos = 0 or Stop.Pos = 0 or Start.Pos > len(character)-Stop.Pos
    then character
    else substring(character, Start.Pos+1, len(character)-Stop.Pos-Start.Pos)
  end

from actor_character
  cross apply (select charindex('[', character)) as Start(Pos)
  cross apply (select charindex(']', reverse(character))) as Stop(Pos)

Upvotes: 2

squawknull
squawknull

Reputation: 5192

select substring(field, charindex('[', field) + 1, charindex(']', field) - charindex('[', field) - 1)

Upvotes: 0

Greg Randall
Greg Randall

Reputation: 921

It sounds like you need a regular expression, to get the data you need out of your source string.

http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html

http://blog.tech-cats.com/2007/09/using-regular-expression-in-sql-server.html

Upvotes: 1

Related Questions