Select String value in SQL Server

Question

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

Your question is unclear. What is your table structure and what are you trying to do? — Cetin Basoz
– Cetin Basoz, Commented Oct 21, 2019 at 16:40
If this is the data in your table, then there is something very wrong with your database design. — Eric
– Eric, Commented Oct 21, 2019 at 16:44

Gordon Linoff · Accepted Answer · 2019-10-21 16:42:46Z

1

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.

answered Oct 21, 2019 at 16:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lopez Lopez Over a year ago

It works writing the string like you posted, but how can i apply it to a Column for example?

Gordon Linoff Over a year ago

@LopezLopez . . . I have no idea what you are asking in your comment. In this query, the column name is str. You can use whatever name you like.

Ian Siñel · Accepted Answer · 2019-10-21 16:48:54Z

0

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

Get the index where your keyword is placed alongside your entire string.
From index, add the number or lenght of characters you used from your keyword.
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.

answered Oct 21, 2019 at 16:48

Ian Siñel

951 silver badge12 bronze badges

Collectives™ on Stack Overflow

Select String value in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related