Tell me how to create a query in SQL. There is a Varchar type field in the table, which contains the following text:

1670 2080 | Product: Plain balcony door | Wood: Solid larch | Profile: IV-68 | Outside color: GW306 ID7006 LW700 62.05.55-50% 2 layers (SLM) | Inside color: GW306 ID7006 LW700 62.05.55-50 % 2 layers (SLM) | Shutter profile: Bevel 20 * | Piece profile ...

I need a SQL query that would output such data in separate fields, dividing the string by the character | :

  • Field 1 - 1670 2080 ,
  • field 2 - Изделие: Обычная балконная дверь штульповая and so on.
  • Does the source line have a fixed number of elements? Those. number of delimiters "|" always the same thing? - Zufir
  • 3
    sql is given to us to store structured data, and not to thrust cvs there. - zb '
  • @ kadekin778 the only thing that can be said here is that you have chosen an extremely unfortunate structure for storing data. It is difficult to imagine in what situation such a frankly crooked solution might be useful. Therefore, the only advice in this situation is to correct the structure of the table (or the entire database) to a more adequate one and start from the result obtained. I think any improvements to the existing structure will only lead to unpleasant consequences - DreamChild
  • The number of separators is variable. I don’t go in there like that. This program writes them down. And I'm trying to rewrite with MSQ in FIREBIRD and work with this data. - kadekin778

5 answers 5

Right away, a violation of 1NF (first normal form) when storing information. Database and SQL are not intended for data manipulation data. The solution to this issue is to bring the database to the first normal form or use the crutch as a user-defined function on T-SQL.

The request to the search engine "TSQL split string" will give a lot of options, for example:

 CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE CHARINDEX('|', @stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX('|', @stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos -1) INSERT INTO @returnList SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos +1, LEN(@stringToSplit)- @pos ) END INSERT INTO @returnList SELECT @stringToSplit RETURN END Ну и использование: SELECT * FROM dbo.splitstring('1670 2080|Изделие: Обычная балконная дверь штульповая|Древесина: Лиственница цельная|Профиль: IV-68|Цвет снаружи: GW306 ID7006 LW700 62.05.55-50% 2 слоя(СЛМ)')
  • 2
    I think there is one potential problem here - if the table is created in this way, then most likely a bunch of similar problems are present in the application code itself. I am more than confident that any change in the database structure will entail a million changes in the application code. However, this is not at all a minus of your answer - DreamChild
  • Thank. Tell me how to apply it now in Delphi. Where you need to register CREATE FUNCTION dbo.splitstring (@stringToSplit VARCHAR (MAX)) - kadekin778
  • @ kadekin778, in the MS SQL database it is possible to write user-defined functions that are stored in the database itself and then called on demand. It is enough to apply once the CREATE FUNCTION dbo.splitstring ... query to the database in any way (from Delphi or via SQL SMS) and this function will remain stored there. Otherwise, only dbo.splitstring () is already used freely as a normal function, to which the necessary string of characters is passed. You can even get it by a subquery: SELECT * FROM dbo.splitstring ((SELECT <field> FROM <table> WHERE uid = 1)) - Alex Krass
  • in MS SQL I know that this is possible, but how to set this function in FIREBIRD - kadekin778
  • Firebird did not work, but the same query in the search engine "firebird split string" produced this option (you only need to change the comma to the required '|' character): stackoverflow - split comma separated string - Alex Krass

Personally, I recently liked the XML from MSSQL, other variants of data presentation are also possible, I understood from the question what should be divided into fields and not into records. I can write both options. I will just make a reservation that there are restrictions on entry (there are `<> & signs), but in most cases it works well. The xml speed is comparable to the speed of the function, perhaps at 1.5-2p slower, but simply and less code.

 declare @c char(1000) set @c = '1670 2080|Изделие: Обычная балконная дверь штульповая|Древесина: Лиственница цельная|Профиль: IV-68|Цвет снаружи: GW306 ID7006 LW700 62.05.55-50% 2 слоя(СЛМ)|Цвет внутри: GW306 ID7006 LW700 62.05.55-50% 2 слоя(СЛМ)|Профиль створки: Скос 20*|Профиль шт.' select x.value('(/a)[1]', 'varchar(50)') a1 , x.value('(/a)[2]', 'varchar(50)') a2, x.value('(/a)[3]', 'varchar(50)') a3, x.value('(/a)[4]', 'varchar(50)') a4, x.value('(/a)[5]', 'varchar(50)') a5 from ( select cast( '<a>' + replace(@c, '|' , '</a><a>') + '</a>' as xml) x ) t

    Add persisted calculated xml field.
    So, too, you can choose, but it is extremely slow, so I will not write the text.

    • Can tex write? >> Add persisted computed xml field. So, too, you can choose, but it is extremely slow, so I will not write the text. - kadekin778

    In addition to the replies already published, the option with recursive OTV:

     Declare @s nvarchar(max) = '1670 2080|Изделие: Обычная балконная дверь штульповая|Древесина: Лиственница цельная|Профиль: IV-68|Цвет снаружи: GW306 ID7006 LW700 62.05.55-50% 2 слоя(СЛМ)' ;with cte (n) as ( select n = 0 union all select n = n + 1 from cte where n <300 ) select substring(@s, n+1 ,lead(n-1, 1 , n*2) over (order by n)-n) as [Описание] from cte where substring(@s,n,1) ='|' or n = 0 option (maxrecursion 300)

    test

    enter image description here

      You solve the problem with the wrong tool. Give the data in this form to the application, and in the application rebuild the resulting array.

      First, even if you find a SQL solution, it will be a crutch.

      Secondly, you have a potential problem with division - it is possible that the delimiter character will fall into one of the parts of the record, so there must surely be a logic for dividing the record into components. And it is better if this logic is somewhere near the code responsible for merging data into one line.