-1
MY|NAME|IS|ABCD|ZHGGG|GSHHS|ASDF|ASDF

The above string is in one table. I have another table in that 8 columns are there(col1,col2,....col8)

I have insert this string without pipe into the 8 columns dynamically:

COL1 COL2 COL3......COL8 MY NAME Is ASDF

I haven't use STRING_SPLIT because I have old version of SQL server.

So I need to create function

I created the function but the string is storred in one column and records insert vertically ...

String
My
Name
Is
.
...
Thom A
  • 88,727
  • 11
  • 45
  • 75
Dhiraj Patil
  • 1
  • Please don't SHOUT at us; it can come across as rude. – Thom A Jun 15 '22 at 10:39
  • Will your string *always* have 8 columns? No more, no less? – Thom A Jun 15 '22 at 10:41
  • Yes sir, but i have to insert the string dynamically... – Dhiraj Patil Jun 15 '22 at 10:45
  • What do you mean by *"insert the string dynamically"*? And is that a "Yes, it will **always** have 8 values, no more, no less."? – Thom A Jun 15 '22 at 10:46
  • -larnu ...hello sir dynamically means string automatically split into 8 columns columns are dynamically created – Dhiraj Patil Jun 15 '22 at 10:48
  • 1
    I think it's time you took the [tour], you seem to be misunderstanding what [so] is. We can't help you, if you won't help us help you... If you won't answer the questions in the comments, you're on your own. – Thom A Jun 15 '22 at 10:49
  • Anyone, can insert this pipe string into 8 different columns – Dhiraj Patil Jun 15 '22 at 10:49
  • Larn - sir question is simpal i have one table with strings with pipes abc|adg|.... i have to insert this words into 8 columns . – Dhiraj Patil Jun 15 '22 at 10:51
  • 3
    @DhirajPatil I don't believe Larnu stated their gender, please don't assume they are "sir" – HoneyBadger Jun 15 '22 at 10:57
  • Ok sorry -HoneyBader – Dhiraj Patil Jun 15 '22 at 11:00
  • Have a look here: https://stackoverflow.com/questions/10914576/t-sql-split-string or here: https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql/1846561#1846561. – Jonas Metzler Jun 15 '22 at 11:03
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Jun 15 '22 at 15:23

1 Answers1

2

If we assume that the value will always contain 8 values, then you can could use some ugly CHARINDEX and SUBSTRINGs, and STUFF for the final column:

DECLARE @YourString varchar(8000) = 'MY|NAME|IS|ABCD|ZHGGG|GSHHS|ASDF|ASDF';


SELECT SUBSTRING(V.YourString,1,CI1.I-1) AS Col1,
       SUBSTRING(V.YourString,CI1.I+1,CI2.I-CI1.I-1) AS Col2,
       SUBSTRING(V.YourString,CI2.I+1,CI3.I-CI2.I-1) AS Col3,
       SUBSTRING(V.YourString,CI3.I+1,CI4.I-CI3.I-1) AS Col4,
       SUBSTRING(V.YourString,CI4.I+1,CI5.I-CI4.I-1) AS Col5,
       SUBSTRING(V.YourString,CI5.I+1,CI6.I-CI5.I-1) AS Col6,
       SUBSTRING(V.YourString,CI6.I+1,CI7.I-CI6.I-1) AS Col7,
       STUFF(V.YourString,1,CI7.I,'') AS Col8
FROM (VALUES(@YourString))V(YourString)
      CROSS APPLY (VALUES(CHARINDEX('|',V.YourString)))CI1(I)
      CROSS APPLY (VALUES(CHARINDEX('|',V.YourString,CI1.I+1)))CI2(I)
      CROSS APPLY (VALUES(CHARINDEX('|',V.YourString,CI2.I+1)))CI3(I)
      CROSS APPLY (VALUES(CHARINDEX('|',V.YourString,CI3.I+1)))CI4(I)
      CROSS APPLY (VALUES(CHARINDEX('|',V.YourString,CI4.I+1)))CI5(I)
      CROSS APPLY (VALUES(CHARINDEX('|',V.YourString,CI5.I+1)))CI6(I)
      CROSS APPLY (VALUES(CHARINDEX('|',V.YourString,CI6.I+1)))CI7(I);

With a string splitter (such as DelimitedSplit8K), that returns the original position, and then use that to conditionally aggregate:

DECLARE @YourString varchar(8000) = 'MY|NAME|IS|ABCD|ZHGGG|GSHHS|ASDF|ASDF';

SELECT MAX(CASE DS.ItemNumber WHEN 1 THEN DS.Item END) AS Col1,
       MAX(CASE DS.ItemNumber WHEN 2 THEN DS.Item END) AS Col2,
       MAX(CASE DS.ItemNumber WHEN 3 THEN DS.Item END) AS Col3,
       MAX(CASE DS.ItemNumber WHEN 4 THEN DS.Item END) AS Col4,
       MAX(CASE DS.ItemNumber WHEN 5 THEN DS.Item END) AS Col5,
       MAX(CASE DS.ItemNumber WHEN 6 THEN DS.Item END) AS Col6,
       MAX(CASE DS.ItemNumber WHEN 7 THEN DS.Item END) AS Col7,
       MAX(CASE DS.ItemNumber WHEN 8 THEN DS.Item END) AS Col8
FROM (VALUES(@YourString))V(YourString)
     CROSS APPLY fn.DelimitedSplit8K(V.YourString,'|') DS
GROUP BY V.YourString; --Or, more likely, your ID.
Thom A
  • 88,727
  • 11
  • 45
  • 75