?
Solved

Split field contents to two

Posted on 1999-12-04
9
Medium Priority
?
489 Views
Last Modified: 2008-03-06
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



0
Comment
Question by:juststeve
  • 6
  • 3
9 Comments
 
LVL 8

Accepted Solution

by:
chigrik earned 400 total points
ID: 2255652
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
 
LVL 8

Expert Comment

by:chigrik
ID: 2255655
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
 

Author Comment

by:juststeve
ID: 2256145
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 8

Expert Comment

by:chigrik
ID: 2257758
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
 

Author Comment

by:juststeve
ID: 2261632
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
 
LVL 8

Expert Comment

by:chigrik
ID: 2262913
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
 

Author Comment

by:juststeve
ID: 2264491
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
 
LVL 8

Expert Comment

by:chigrik
ID: 2264742
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
 
LVL 8

Expert Comment

by:chigrik
ID: 2264749
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question