Split field contents to two

A couple months ago I asked for help constructing a Stored Procedure - simonsabin helped me out with it in the thread titled My First Stored Procedure.

Now I'm looking to refine it. In a nutshell I'm massaging what had been imported from an .rtf file into a well-formed table.

My task now is to split the content of one field into two. myChannel has content like: 101 - DTV where 101 is the channel number and DTV is the channel name. I need to

1) create a new field in the db called myChannelname

2) parse the existing field (myChannel) move the characters that follow the '- ' (dash; space) to that newly created field

3) strip all but the the 3 numerical characters in myChannel

Here's the existing SP:

CREATE PROCEDURE UpDTVBlanks
AS

DELETE FROM tblDTV
WHERE (myTime = N'Time')

DECLARE @myChannel varchar(30),
        @myDay varchar(30)
DECLARE @myLastChannel varchar(30),
        @myLastDay varchar(30)


DECLARE cTable CURSOR FOR
    SELECT myChannel, myDay
      FROM tblDTV
       FOR UPDATE

Open cTable

FETCH cTable INTO @myChannel, @myDay
WHILE @@FETCH_STATUS <> -1
  BEGIN
    UPDATE tblDTV SET
      myChannel = ISNULL(myChannel,@myLastChannel),
      myDay =ISNULL(myDay,@myLastDay)
     WHERE CURRENT OF cTable
     
   SELECT @myLastChannel=ISNULL(@myChannel,@myLastChannel),
      @mylastDay = ISNULL(@myDay,@myLastDay)

FETCH cTable INTO @myChannel, @myDay
  END

CLOSE cTable
DEALLOCATE cTable



juststeveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chigrikCommented:
It's my test example:

CREATE TABLE tblDTV (
        ID int IDENTITY (1, 1) NOT NULL ,
        myChannel varchar (30) NULL ,
        myDay varchar (30) NULL ,
        myChannelname varchar (30) NULL
)
GO

ALTER TABLE tblDTV WITH NOCHECK ADD
        CONSTRAINT PK_tblDTV PRIMARY KEY  NONCLUSTERED
        (
                ID
        )
GO

ALTER TABLE tblDTV ADD myChannelname VARCHAR(30) NULL
GO

--it's insert example (for my test only)
insert into tblDTV (myChannel, myDay) values ('101 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('102 - MTV', '10/10/1999')
insert into tblDTV (myChannel, myDay) values ('103 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('104 - MTV', '11/12/1999')
GO

CREATE PROCEDURE sp_split  AS
set nocount on
update tblDTV set
myChannelname = LTRIM(RTRIM(SUBSTRING(myChannel, PATINDEX('%-%', myChannel) + 1, LEN(myChannel)))),
myChannel = LTRIM(RTRIM(STUFF(myChannel, PATINDEX('%-%', myChannel), LEN(myChannel),'')))
where myChannel like '%-%'
GO


Now simply start:

select * from tblDTV
GO
exec sp_split
GO
select * from tblDTV
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chigrikCommented:
Oh, excuse me.

It's my test example:

CREATE TABLE tblDTV (
        ID int IDENTITY (1, 1) NOT NULL ,
        myChannel varchar (30) NULL ,
        myDay varchar (30) NULL
)
GO

ALTER TABLE tblDTV WITH NOCHECK ADD
        CONSTRAINT PK_tblDTV PRIMARY KEY  NONCLUSTERED
        (
                ID
        )
GO

ALTER TABLE tblDTV ADD myChannelname VARCHAR(30) NULL
GO

--it's insert example (for my test only)
insert into tblDTV (myChannel, myDay) values ('101 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('102 - MTV', '10/10/1999')
insert into tblDTV (myChannel, myDay) values ('103 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('104 - MTV', '11/12/1999')
GO

CREATE PROCEDURE sp_split  AS
set nocount on
update tblDTV set
myChannelname = LTRIM(RTRIM(SUBSTRING(myChannel, PATINDEX('%-%', myChannel) + 1, LEN(myChannel)))),
myChannel = LTRIM(RTRIM(STUFF(myChannel, PATINDEX('%-%', myChannel), LEN(myChannel),'')))
where myChannel like '%-%'
GO


Now simply start:

select * from tblDTV
GO
exec sp_split
GO
select * from tblDTV
GO

0
juststeveAuthor Commented:
Thankx chigrik,

Looking at your code I understand how it fulfills my original question but I'm going to play 'moving target' on you.

Having spent a bit more time looking at all the data I've uncovered incidents of fields where only the first 3 characters are present. Meaning code that relies on finding %-% will fail.

The first three characters are always the channel number so the code should become:

myChannel = LTRIM(RTRIM(STUFF(myChannel, 4, LEN(myChannel),'')))

Yes?

But this is where I'm going to make someone earn the extra points <g>

Even _when the second clause of 'channel_num - channel_name' exists the name is only an 3 or 4 character abbreviation. It would be more elegant to return the full title. That information exists in a seperate table where structure is as follows:

tblDTVChannels
myCh_num -  int & Identity
myCh_name - nvarchar
..more fields
..
..

So the question becomes:

1) Strip myChannel down to the first 3 characters.

2) Use those characters to find the matching channel name from tblDTVChannels.myCh_name and insert it into tblDTV.myChannelname (a field that doesn't exisit when the script begins.

Further, this runs weekly so I need see how this new code gets merged with the existing stored procedure [UpDTVBlanks] or how to call the new procedure from that one.

thankx
--steve...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chigrikCommented:
It's my example:

CREATE TABLE tblDTV (
        ID int IDENTITY (1, 1) NOT NULL ,
        myChannel varchar (30) NULL ,
        myDay varchar (30) NULL
)
GO

CREATE TABLE tblDTVChannels (
        myCh_num int NOT NULL ,
        myCh_name nvarchar (50) NULL
)
GO


ALTER TABLE tblDTV WITH NOCHECK ADD
        CONSTRAINT PK_tblDTV PRIMARY KEY  NONCLUSTERED
        (
                ID
        )
GO


ALTER TABLE tblDTVChannels WITH NOCHECK ADD
        CONSTRAINT PK_tblDTVChannels PRIMARY KEY  NONCLUSTERED
        (
                myCh_num
        )
GO

--it's insert example (for my test only)
insert into tblDTV (myChannel, myDay) values ('101 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('102 - MTV', '10/10/1999')
insert into tblDTV (myChannel, myDay) values ('103 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('104 - MTV', '11/12/1999')
GO

insert into tblDTVChannels (myCh_num, myCh_name) values (101, 'Full DTV')
insert into tblDTVChannels (myCh_num, myCh_name) values (102, 'Full MTV')
insert into tblDTVChannels (myCh_num, myCh_name) values (103, 'Full DTV')
insert into tblDTVChannels (myCh_num, myCh_name) values (104, 'Full MTV')
GO

CREATE PROCEDURE sp_split  AS
if not exists (select * from syscolumns where id = object_id(N'[dbo].[tblDTV]') and name = 'myChannelname')
ALTER TABLE tblDTV ADD myChannelname NVARCHAR(50) NULL
set nocount on
EXEC ('update tblDTV set
     myChannel = LTRIM(RTRIM(STUFF(myChannel, 4, LEN(myChannel),''''))),
     myChannelname = myCh_name
FROM tblDTV, tblDTVChannels
    WHERE CAST(myCh_num as varchar(30)) =  LTRIM(RTRIM(STUFF(myChannel, 4, LEN(myChannel),'''')))')
GO


Now simply start:

select * from tblDTV
GO
exec sp_split
GO
select * from tblDTV
GO


If you want to combine all of this in one sp, you may do the following:


CREATE PROCEDURE UpDTVBlanks
AS

DELETE FROM tblDTV
WHERE (myTime = N'Time')

DECLARE @myChannel varchar(30),
        @myDay varchar(30)
DECLARE @myLastChannel varchar(30),
        @myLastDay varchar(30)


DECLARE cTable CURSOR FOR
    SELECT myChannel, myDay
      FROM tblDTV
       FOR UPDATE

Open cTable

FETCH cTable INTO @myChannel, @myDay
WHILE @@FETCH_STATUS <> -1
  BEGIN
    UPDATE tblDTV SET
      myChannel = ISNULL(myChannel,@myLastChannel),
      myDay =ISNULL(myDay,@myLastDay)
     WHERE CURRENT OF cTable
       
   SELECT @myLastChannel=ISNULL(@myChannel,@myLastChannel),
      @mylastDay = ISNULL(@myDay,@myLastDay)

FETCH cTable INTO @myChannel, @myDay
  END

CLOSE cTable
DEALLOCATE cTable

if not exists (select * from syscolumns where id = object_id(N'[dbo].[tblDTV]') and name = 'myChannelname')
ALTER TABLE tblDTV ADD myChannelname NVARCHAR(50) NULL
set nocount on
EXEC ('update tblDTV set
     myChannel = LTRIM(RTRIM(STUFF(myChannel, 4, LEN(myChannel),''''))),
     myChannelname = myCh_name
FROM tblDTV, tblDTVChannels
    WHERE CAST(myCh_num as varchar(30)) =  LTRIM(RTRIM(STUFF(myChannel, 4, LEN(myChannel),'''')))')

0
juststeveAuthor Commented:
Sorry chigrink....things aren't working. First I tried pasting in the merged version and executing that. By all appearences the added code didn't run. I didn't get any errors but there was no new field added and myChannel was untouched.

then I executed sp_split and found the field _was added and some myChannel were stripped and some myChannelname were correctly merge but other records myChannel was untouched and <null>s were in myChannelname.

Looking at:
WHERE CAST(myCh_num as varchar(30)) =  LTRIM(RTRIM(STUFF(myChannel, 4, LEN(myChannel),'''')))')

the first expression in the CAST refers to myCh_num as varchar(30)...I assume that's a temp conversion so as to match the field type of tblDTV.myChannel, yes?

perhaps the problem is the second arg of STUFF - '4'. Is that a zero-based counter? The field will always contain at least 3 characters but not always more.

[shrug]

thankx
--steve...
0
chigrikCommented:
Excuse me steve, you absolutely right, I checked my example when myChannel was more than 3 characters by length. This example not contain this error (I so think):

CREATE TABLE tblDTV (
        ID int IDENTITY (1, 1) NOT NULL ,
        myChannel varchar (30) NULL ,
        myDay varchar (30) NULL
)
GO

CREATE TABLE tblDTVChannels (
        myCh_num int NOT NULL ,
        myCh_name nvarchar (50) NULL
)
GO


ALTER TABLE tblDTV WITH NOCHECK ADD
        CONSTRAINT PK_tblDTV PRIMARY KEY  NONCLUSTERED
        (
                ID
        )
GO


ALTER TABLE tblDTVChannels WITH NOCHECK ADD
        CONSTRAINT PK_tblDTVChannels PRIMARY KEY  NONCLUSTERED
        (
                myCh_num
        )
GO

--it's insert example (for my test only)
insert into tblDTV (myChannel, myDay) values ('101 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('101 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('102 - MTV', '10/10/1999')
insert into tblDTV (myChannel, myDay) values ('103 - DTV', '11/11/1999')
insert into tblDTV (myChannel, myDay) values ('104 - MTV', '11/12/1999')
insert into tblDTV (myChannel, myDay) values ('105', '11/11/1999')

GO

insert into tblDTVChannels (myCh_num, myCh_name) values (101, 'Full DTV')
insert into tblDTVChannels (myCh_num, myCh_name) values (102, 'Full MTV')
insert into tblDTVChannels (myCh_num, myCh_name) values (103, 'Full DTV')
insert into tblDTVChannels (myCh_num, myCh_name) values (104, 'Full MTV')
insert into tblDTVChannels (myCh_num, myCh_name) values (105, 'Full MTV')
GO

CREATE PROCEDURE sp_split  AS
if not exists (select * from syscolumns where id = object_id(N'[dbo].[tblDTV]') and name = 'myChannelname')
ALTER TABLE tblDTV ADD myChannelname NVARCHAR(50) NULL
set nocount on
EXEC ('update tblDTV set
     myChannel = SUBSTRING(LTRIM(myChannel), 1, 3),
     myChannelname = myCh_name
FROM tblDTV, tblDTVChannels
    WHERE CAST(myCh_num as varchar(30)) =  SUBSTRING(LTRIM(myChannel), 1, 3)')
GO


Now simply start:

select * from tblDTV
GO
exec sp_split
GO
select * from tblDTV
GO


Try this (I not know why do not work my previous example):


CREATE PROCEDURE UpDTVBlanks
AS

DELETE FROM tblDTV
WHERE (myTime = N'Time')

DECLARE @myChannel varchar(30),
        @myDay varchar(30)
DECLARE @myLastChannel varchar(30),
        @myLastDay varchar(30)


DECLARE cTable CURSOR FOR
    SELECT myChannel, myDay
      FROM tblDTV
       FOR UPDATE

Open cTable

FETCH cTable INTO @myChannel, @myDay
WHILE @@FETCH_STATUS <> -1
  BEGIN
    UPDATE tblDTV SET
      myChannel = ISNULL(myChannel,@myLastChannel),
      myDay =ISNULL(myDay,@myLastDay)
     WHERE CURRENT OF cTable
       
   SELECT @myLastChannel=ISNULL(@myChannel,@myLastChannel),
      @mylastDay = ISNULL(@myDay,@myLastDay)

FETCH cTable INTO @myChannel, @myDay
  END

CLOSE cTable
DEALLOCATE cTable

-- Simply add this string into your sp UpDTVBlanks
EXEC sp_split
GO

I hope this merged version will work.
0
juststeveAuthor Commented:
Well chigrik, we're getting closer. The script works everywhere except when a match for myChannelname doesn't exist in the other table.

Looking at this from a database design point of view I now see that it's incorrect of me to want to import a myChannelname. That merely duplicates information that is easily retrieved via a link/query between the two tables.

Therefore our task simplifies to the point of merely stripping all characters that may or may not trail the third character.

May we then, reduce the code to:

CREATE PROCEDURE sp_split  AS
exec('update tblDTV set
     myChannel = SUBSTRING(LTRIM(myChannel), 1, 3
FROM tblDTV')
GO
0
chigrikCommented:
In this case, you may run this:

CREATE PROCEDURE sp_split  AS
update tblDTV set myChannel = SUBSTRING(LTRIM(myChannel), 1, 3)

By the way, what about points?
0
chigrikCommented:
In this case, you may run this:

CREATE PROCEDURE sp_split  AS
update tblDTV set myChannel = SUBSTRING(LTRIM(myChannel), 1, 3)

By the way, what about points?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.