CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Lopez Lopez
Lopez Lopez

Reputation: 5

Select String value in SQL Server

I have this fields in a SQL Table 

[header]nombre=LPP Expreso LD Santiago 2 Descargas-01-10-2007 codigo=ACGRZ target=0 activo=0
[header]nombre=BS.AS / ORAN 01-07-2019 codigo=ACJEX target=0 activo=1 VigenciaDesde=01/07/2019
[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10 DEV codigo=ACLGO target=0 activo=1 VigenciaD

How can i do a query to get the String: codigo=XXXXXX ? The text codigo is always in a diferente position

Upvotes: 0

Views: 5696

Answers (2)

Ian Siñel
Ian Siñel

Reputation: 95

Play around SQL built in functions, CHARINDEX and SUBSTRING. Logically, the following pseudocode should work:

  1. Get the index where your keyword is placed alongside your entire string.
  2. From index, add the number or lenght of characters you used from your keyword.
  3. Substring the entire text from result of step number 2 up until to the number of desired lenght of text. If you're text is fixed then it would be easier.

See CHARINDEX and SUBSTRING.

Hope this helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

SQL Server has poor string processing functionality. That is why it is better to process data when it goes into the database.

That said, you can do what you want. Here is one method:

select t.*, left(v1.str, charindex(' ', v1.str + ' ')) as codigo_str
from (values ('[header]nombre=ATC Cargas MZA/TUC/SGO.TN.03/03/10 DEV codigo=ACLGO target=0 activo=1 VigenciaD')) t(str) cross apply
     (values (stuff(t.str, 1, charindex('codigo=', t.str + 'coldigo=') - 1, ''))) v1(str);

This chops off the string before the 'codigo='. It then uses left() to take everything up to the first space.

The use of concatenation within charindex() makes this safe if 'codigo=' is not in the string or not followed by a space.

Upvotes: 1

Related Questions