sql charindex split string

[Willem]

Hello

I am quite hopeless and of course a newbe.

The situation: Sql2k / query

I would like it ot break down the following string:

2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094

Whereby:
Year = '2004 Inventory'
plant= 'Ex.Plant Farm1'
cut = '1st Cut'
grade = 'Premium'
lot# = '0094'

It is always seperate by ':', but it can be 5 fields or 4 or 3 and so
on

code to create the view:

CREATE VIEW dbo.TEST
AS
SELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -
1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))

0 THEN LTRIM(SUBSTRING(FullName,

CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',
CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',
FullName) - 1)) ELSE NULL END AS Plant, CASE
WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THEN
LTRIM(SUBSTRING(FullName,
CHARINDEX(':', FullName + ':', CHARINDEX(':',
FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',
Fullname) + 1) - CHARINDEX(':',
FullName+':') - 1)) ELSE NULL END AS [Cut]
FROM dbo.ItemInventory

Can anyone help me with this? I am stuck half the way and get for cut
the rest of the string: '1st Cut:Premium:0094'

Thanks!

[Erland Sommarskog]

Willem (wi********@hotmail.com) writes:

I am quite hopeless and of course a newbe.

The situation: Sql2k / query

I would like it ot break down the following string:

2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094

Whereby:
Year = '2004 Inventory'
plant= 'Ex.Plant Farm1'
cut = '1st Cut'
grade = 'Premium'
lot# = '0094'

It is always seperate by ':', but it can be 5 fields or 4 or 3 and so
on

Obviously someone fluked his data modelling. It's never a good idea
to store more than piece of data in one column. So when you need the
pieces it becomes messy.

Here is a query for your task. I opted as a number of nested derived
tables:

CREATE TABLE inventory (fullname varchar(60) NOT NULL)
go
INSERT inventory(fullname)
VALUES ('2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094')
INSERT inventory(fullname)
VALUES ('2004 Inventory')
INSERT inventory(fullname)
VALUES ('2004 Inventory:Ex.Plant Farm1:1st Cut')
go
SELECT inventory, plant = nullif(plant, ''), cut = nullif(cut, ''),
grade = nullif(grade, ''), lot# = nullif(lot#, '')
FROM
(SELECT inventory, plant, cut,
left(rest, charindex(':', rest + ':') - 1) AS grade,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS lot#
FROM
(SELECT inventory, plant,
left(rest, charindex(':', rest + ':') - 1) AS cut,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS rest
FROM
(SELECT inventory,
left(rest, charindex(':', rest + ':') - 1) AS plant,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS rest
FROM
(SELECT left(fullname, charindex(':', fullname + ':') - 1) AS inventory,
substring(fullname, charindex(':', fullname + ':') + 1,
len(fullname)) AS rest
FROM inventory) AS a) AS b) AS c) AS d
go
DROP TABLE inventory

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Willem]

Hi Erland

Really thanks for your answer. Finally found some time to look into
it.

I think I have to explain more about the situation.

The information is...from Quickbooks Enterprise and I use a small tool
http://www.synergration.com/accessbooksrt/default.htm

And at least a tool what pulls the information from quickbooks and put
it into
access or sql. In my case SQL. And synchronizing every 5 minutes.
That works fine so far.

The field ("FullName") I would like to break down or split is from
table "ItemInventory". This field is used in every table
(sales/purchase/item
and so on)

I would like it to setup our clients to use it through a sql-dsn and
create pivot tables in excel (based on the refreshed tables in sql).
Not sure yet, or to create asp pages. So I need actually to prepare
the
information in sql and from there I can use it on the clients.
(reason is...limitation of quickbooks...10 users)

So what is the best way to go? to create a table? but how will it be
updated?
And to be honest I feel that I need more basic knowledge of sql.
Right now I am actually struggeling and looking for the best way to
get the information prepared inside sql.

Could you please give me some direction?

thanks!!

Willem


Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>...

Willem (wi********@hotmail.com) writes:

I am quite hopeless and of course a newbe.

The situation: Sql2k / query

I would like it ot break down the following string:

2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094

Whereby:
Year = '2004 Inventory'
plant= 'Ex.Plant Farm1'
cut = '1st Cut'
grade = 'Premium'
lot# = '0094'

It is always seperate by ':', but it can be 5 fields or 4 or 3 and so
on

Obviously someone fluked his data modelling. It's never a good idea
to store more than piece of data in one column. So when you need the
pieces it becomes messy.

Here is a query for your task. I opted as a number of nested derived
tables:

CREATE TABLE inventory (fullname varchar(60) NOT NULL)
go
INSERT inventory(fullname)
VALUES ('2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094')
INSERT inventory(fullname)
VALUES ('2004 Inventory')
INSERT inventory(fullname)
VALUES ('2004 Inventory:Ex.Plant Farm1:1st Cut')
go
SELECT inventory, plant = nullif(plant, ''), cut = nullif(cut, ''),
grade = nullif(grade, ''), lot# = nullif(lot#, '')
FROM
(SELECT inventory, plant, cut,
left(rest, charindex(':', rest + ':') - 1) AS grade,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS lot#
FROM
(SELECT inventory, plant,
left(rest, charindex(':', rest + ':') - 1) AS cut,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS rest
FROM
(SELECT inventory,
left(rest, charindex(':', rest + ':') - 1) AS plant,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS rest
FROM
(SELECT left(fullname, charindex(':', fullname + ':') - 1) AS inventory,
substring(fullname, charindex(':', fullname + ':') + 1,
len(fullname)) AS rest
FROM inventory) AS a) AS b) AS c) AS d
go
DROP TABLE inventory

[Willem]

Hi Erland

Really thanks for your answer. Finally found some time to look into
it.

I think I have to explain more about the situation.

The information is...from Quickbooks Enterprise and I use a small tool
http://www.synergration.com/accessbooksrt/default.htm

And at least a tool what pulls the information from quickbooks and put
it into
access or sql. In my case SQL. And synchronizing every 5 minutes.
That works fine so far.

The field ("FullName") I would like to break down or split is from
table "ItemInventory". This field is used in every table
(sales/purchase/item
and so on)

I would like it to setup our clients to use it through a sql-dsn and
create pivot tables in excel (based on the refreshed tables in sql).
Not sure yet, or to create asp pages. So I need actually to prepare
the
information in sql and from there I can use it on the clients.
(reason is...limitation of quickbooks...10 users)

So what is the best way to go? to create a table? but how will it be
updated?
And to be honest I feel that I need more basic knowledge of sql.
Right now I am actually struggeling and looking for the best way to
get the information prepared inside sql.

Could you please give me some direction?

thanks!!

Willem


Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>...

Willem (wi********@hotmail.com) writes:

I am quite hopeless and of course a newbe.

The situation: Sql2k / query

I would like it ot break down the following string:

2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094

Whereby:
Year = '2004 Inventory'
plant= 'Ex.Plant Farm1'
cut = '1st Cut'
grade = 'Premium'
lot# = '0094'

It is always seperate by ':', but it can be 5 fields or 4 or 3 and so
on

Obviously someone fluked his data modelling. It's never a good idea
to store more than piece of data in one column. So when you need the
pieces it becomes messy.

Here is a query for your task. I opted as a number of nested derived
tables:

CREATE TABLE inventory (fullname varchar(60) NOT NULL)
go
INSERT inventory(fullname)
VALUES ('2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094')
INSERT inventory(fullname)
VALUES ('2004 Inventory')
INSERT inventory(fullname)
VALUES ('2004 Inventory:Ex.Plant Farm1:1st Cut')
go
SELECT inventory, plant = nullif(plant, ''), cut = nullif(cut, ''),
grade = nullif(grade, ''), lot# = nullif(lot#, '')
FROM
(SELECT inventory, plant, cut,
left(rest, charindex(':', rest + ':') - 1) AS grade,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS lot#
FROM
(SELECT inventory, plant,
left(rest, charindex(':', rest + ':') - 1) AS cut,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS rest
FROM
(SELECT inventory,
left(rest, charindex(':', rest + ':') - 1) AS plant,
substring(rest, charindex(':', rest + ':') + 1,
len(rest)) AS rest
FROM
(SELECT left(fullname, charindex(':', fullname + ':') - 1) AS inventory,
substring(fullname, charindex(':', fullname + ':') + 1,
len(fullname)) AS rest
FROM inventory) AS a) AS b) AS c) AS d
go
DROP TABLE inventory

[Erland Sommarskog]

Willem (wi********@hotmail.com) writes:

The information is...from Quickbooks Enterprise and I use a small tool
http://www.synergration.com/accessbooksrt/default.htm
That site is not availble from my corner of the world right now. Nor
I have ever before heard of Quickbooks. So it's very difficult for me
to give any useful answers.
And at least a tool what pulls the information from quickbooks and put
it into
access or sql. In my case SQL. And synchronizing every 5 minutes.
That works fine so far.

I would suggest that you should perform the split before you get into
the database. As you have experienced, this is not a funny thing to
do in SQL. Since I don't know anything about the source for the data,
nor about your tool, it's difficult to say how would you do it. All
I can say is that myself, I would have done it in Perl. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[pb648174]

Below is a UDF to take a comma delim value and return in table format,
the split results.. Use like select * from dbo.Split('1,2,3,3',',')

CREATE FUNCTION dbo.Split(@sText varchar(8000), @sDelim varchar(20) = '
')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx int,
@value varchar(8000),
@bcontinue bit,
@iStrike int,
@iDelimlength int

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

if(Len(@sText) = 0)
return

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element
and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you canÆt find the delimiter in the text, @sText is the last
value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO