SQL Query to split strings into multiple fields and multiple records

[hopper44]

I have a table defined as follows:

CREATE TABLE KT ( AccountNo VARCHAR(20), StockNames VARCHAR(4000) );

100 "International Business Machines | Conoco Philips | Exxon Inc | American Telegraph and Telephone | McDonalds Inc | JC Penny | Hewlett Packard "
200 "Microsoft Inc | Eastman Kodak | Merck Inc | Pfizer Inc | Dell Inc | Genworth Financial | Synovus Financial | Texas Instruments|"
.
.



I need to split the records into another table (let's call it KT_Output) so that the stock names are put in 2 fields of size 50 chars.
We will fill up as many stock names that can fit in the 50 char field, without splitting the name halfway. The delimiter character '|'
designates the end of a stock name. Another record with the same account number is created if there are more stocks that can fit
in Stock1 and Stock2 fields.

CREATE TABLE KT_Output ( AccountNo VARCHAR(20), Stock1 Varchar(50), Stock2 Varchar(50) )

So, we should have something like the following output:

AccountNo Stock1 Stock2
100 "International Business Machines | Conoco Philips" "Exxon Inc | American Telegraph and Telephone "
100 "McDonalds Inc | JC Penny | "Hewlett Packard"
200 "Microsoft Inc | Eastman Kodak | Merck Inc" "Pfizer Inc | Dell Inc | Genworth Financial"
200 "Synovus Financial | Texas Instruments"

How can I do this?

Thanks in advance for your reply.

 

[simian336]

Ok, I'll bite, Why? : )

I recently did something similar (without the 50 character deal) and wound up using a cursor.

Simi

 

[markros]

I remember there was a script by Peter Larsson that solves this problem (of smart splitting). Try googling and I'll ask him about it.

PluralSight Learning Library

 

[markros]

This is the article

http://weblogs.sqlteam.com/peterl/archive/2009/03/18/Human-readable-string-truncation.aspx

PluralSight Learning Library

 

[travisbrown]

Well, here is a UDF you can use as a starting point. You'd have to monkey around to try find the right split point.

Might be best to split record items into rows using something like below, then concatenate back together using STUFF with a test on each concatenation so you don't exceed 50, then use a INSTRINGREV on the last | of that first field as a start point for STUFFing the second field, and then same on the second column if exceeds 100.

If this was a one-time thing, I'd probably do this in a scripting language outside SQL simply because I'd find it faster.

I'm curious why you wouldn't just normalize the data into individual records for storage. Then if you needed it output into your desired format above, shape it only for that purpose.

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udfSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END