How to manipulate column data to place in a temporary table  
Author Message
MacCaillean





PostPosted: Wed May 24 06:03:34 CDT 2006 Top

SQL Server Developer >> How to manipulate column data to place in a temporary table

Hi all.

I tried posting a question earlier this week regarding this topic but
didn't really know where to start. Anith pointed me in the right
direction (thank you, Anith) and I have now done a little more research
into exactly what I'm trying to achieve.

What I'm trying to do is copy a column of data, [Description], from
'Table1' to a column named [Value] in a temporary table. The data in
the [Description] column will use the following format;

'ZZZ YYY XXX WWW'

When I copy the data to the 'Value' column in the temporary table,
however, I only want 'WWW' - not 'ZZZ YYY XXX WWW'. Here is an excerpt
from the script...

BEGIN
CREATE TABLE #temporary_grouping_holding_table_0087635
(
GroupID int,
ClientGroupValueID int
IDENTITY(1,1),
[Value] nvarchar(125)
)

INSERT INTO #temporary_grouping_holding_table_0087635
(GroupID, [Value])

FROM Table1
Group By [Description]

Insert Into ClientGroupValue
(GroupID, ClientGroupValueID, [Value])
SELECT GroupID, ClientGroupValueID, [Value]
FROM #temporary_grouping_holding_table_0087635

END

How can I modify the above script to copy only the required data to the
[Value] column in the temporary table? How can I modify the above
script to copy only 'ZZZ' or 'YYY' or 'XXX' to the [Value] column in
the temporary table?

I sincerely hope the information I have provided will suffice!!

Thanks.

SQL Server240  
 
 
Roy





PostPosted: Wed May 24 06:03:34 CDT 2006 Top

SQL Server Developer >> How to manipulate column data to place in a temporary table The only question I have is whether the format 'ZZZ YYY XXX WWW' is
absolutely fixed in length and position, or if it is simply a pattern
delimited by blanks, as in 'ZZZZZ YY X WWWWWWW'. If the positions
are absolutely fixed this is quite simple:









If, on the other hand, we need to parse the string on the blanks it
gets a bit more complicated. The key is knowing where the blanks are.
If the number of blanks is always three, giving four sub-fields in the
column, then something like the following shows how to derive them.
The process uf substituting those values into the appropriate spots in
each SUBSTRING - adding and subtracting 1 as appropriate - I leave to
you.







Blank3

Blank1 Blank2 Blank3
----------- ----------- -----------
4 8 12

Roy Harvey
Beacon Falls, CT



>Hi all.
>
>I tried posting a question earlier this week regarding this topic but
>didn't really know where to start. Anith pointed me in the right
>direction (thank you, Anith) and I have now done a little more research
>into exactly what I'm trying to achieve.
>
>What I'm trying to do is copy a column of data, [Description], from
>'Table1' to a column named [Value] in a temporary table. The data in
>the [Description] column will use the following format;
>
> 'ZZZ YYY XXX WWW'
>
>When I copy the data to the 'Value' column in the temporary table,
>however, I only want 'WWW' - not 'ZZZ YYY XXX WWW'. Here is an excerpt
>from the script...
>
> BEGIN
> CREATE TABLE #temporary_grouping_holding_table_0087635
> (
> GroupID int,
> ClientGroupValueID int
> IDENTITY(1,1),
> [Value] nvarchar(125)
> )
>
> INSERT INTO #temporary_grouping_holding_table_0087635
> (GroupID, [Value])

> FROM Table1
> Group By [Description]
>
> Insert Into ClientGroupValue
> (GroupID, ClientGroupValueID, [Value])
> SELECT GroupID, ClientGroupValueID, [Value]
> FROM #temporary_grouping_holding_table_0087635

> END
>
>How can I modify the above script to copy only the required data to the
>[Value] column in the temporary table? How can I modify the above
>script to copy only 'ZZZ' or 'YYY' or 'XXX' to the [Value] column in
>the temporary table?
>
>I sincerely hope the information I have provided will suffice!!
>
>Thanks.
 
 
stephen





PostPosted: Wed May 24 07:37:39 CDT 2006 Top

SQL Server Developer >> How to manipulate column data to place in a temporary table Thanks so much for the quick and concise reply, Roy.

You've actually supplied answers to both of the possible scenarios that
I have been presented with!! Thanks.

The only remaining question I have is thus...does the above answer only
cover a single row in a column that has subsequently been assigned to a

what I'm saying is, exactly how do i modify...

INSERT INTO #temporary_grouping_holding_table_0087635
(GroupID, [Value])

FROM Table1
Group By [Description]

My apologies if the answer is blatantly clear, however, I know very
little on this subject. Many thanks.

Steve.

 
 
Jim





PostPosted: Wed May 24 08:30:25 CDT 2006 Top

SQL Server Developer >> How to manipulate column data to place in a temporary table just use substring on the column in your select/insert statement (or
whichever function best suites your need).

INSERT INTO #temporary_grouping_holding_table_0087635
(GroupID, [Value])

FROM Table1
Group By [Description]



> Thanks so much for the quick and concise reply, Roy.
>
> You've actually supplied answers to both of the possible scenarios that
> I have been presented with!! Thanks.
>
> The only remaining question I have is thus...does the above answer only
> cover a single row in a column that has subsequently been assigned to a

> what I'm saying is, exactly how do i modify...
>
> INSERT INTO #temporary_grouping_holding_table_0087635
> (GroupID, [Value])

> FROM Table1
> Group By [Description]
>
> My apologies if the answer is blatantly clear, however, I know very
> little on this subject. Many thanks.
>
> Steve.
>


 
 
stephen





PostPosted: Wed May 24 08:47:38 CDT 2006 Top

SQL Server Developer >> How to manipulate column data to place in a temporary table jim. i think that may be exactly what i was after! is there any way
to deal with the 4th field in the row if it varies in length? the
first 3 fields in the row will always be 3 characters long. the 4th
field, however, may vary in length. thanks!!

 
 
Jim





PostPosted: Wed May 24 09:43:09 CDT 2006 Top

SQL Server Developer >> How to manipulate column data to place in a temporary table You will have to post some sample data and a full explanation of how you are
finding the value you need. It sounds like your database is not designed
correctly for this data, however, as all of these values should already be
stored in seperate columns.

The ideal solution is to change your database design to insure that multiple
values are never stored together in a single column, btu aree placed in
multiple columns so you won't have these issues.




> jim. i think that may be exactly what i was after! is there any way
> to deal with the 4th field in the row if it varies in length? the
> first 3 fields in the row will always be 3 characters long. the 4th
> field, however, may vary in length. thanks!!
>