Solved

Writing a dynamic SQL query that splits the contents of a column

Posted on 2008-06-09
6
270 Views
Last Modified: 2010-03-19
I have a database in SQL Server 2005. There are 4 columns in the database that are of relevance to my question, we'll call them Combined, A, B, and C. These columns contain measurements, where all 4 are varchar fields.

I need to loop through about 3500 records where Combined is populated but A, B, and C are not. So, basically what I need to do is take the string contained within Combined, which will look something like:
11" x 22" x 3-1/4", split it, trim it, and take just the numeric portions of the combined string and put them in their corresponding cells. So, in this instance, 11 would go in A, 22 would go in B and 3-1/4 would go in C. By trimming, I mean the ", x and whitespace would all be removed.

I would imagine this is rather a simple query to write, but my T-SQL is mediocre at best. Can anyone help me write a query to do this?

Thanks!
0
Comment
Question by:nuvium
6 Comments
 

Author Comment

by:nuvium
ID: 21746468
Wait, before anyone answers this question, I just doublechecked my notes, and I HAVE IT BACKWARDS. The basis of what im trying to do is the same (and I definitely still need help with the query), but I have it in the opposite order!

Instead, I have the three columns, A, B and C populated with numbers. (using the above example, 11, 22, 3-1/4). I need to instead of split it, combine it using the following format : 11" x 22" 3-1/4".

So the formula is Number 1(") white space (x) white space Number 2(")  (x) Number 3(")

Instead of splitting one column into 3, I am combining 3 columns into one.
0
 
LVL 19

Expert Comment

by:elimesika
ID: 21746599
HI

See code below (assuming your table name is T)
select A,B,C, A + ' X ' + B + ' X ' + C  as D from T

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21746621
Add a computed column to the table (once) to materialize that column whenever it is needed.

ALTER TABLE tablename
    ADD compoundColumn AS colA + '" x ' + colB + '" x ' + colC + '"'

From then on, use that name just like any other col, for example:


SELECT compoundColumn
FROM tablename
WHERE compoundColumn LIKE '%...%'
ORDER BY compoundColumn
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 5

Expert Comment

by:scgstuff
ID: 21746646
If the fields are int (or any other format) you will need to do this:

select cast(A as varchar) + ' X ' + cast(B as varchar) + ' X ' + cast(C as varchar) as D from T

Shawn
0
 
LVL 2

Accepted Solution

by:
floook earned 500 total points
ID: 21747512
To account for blank values and nulls in any of the columns you'll need something like this:

UPDATE tableName
   SET Combined =

       CASE
            WHEN LEN(isNull(A, '')) > 0 THEN A + '" x '
            ELSE ''
       END

        +

        CASE
             WHEN LEN(isNull(B, '')) > 0 THEN B + '" x '
             ELSE ''
        END

        +

        CASE
             WHEN LEN(isNull(C, '')) > 0 THEN C + '"'
             ELSE ''
        END

I ran it on a sample table and the results are as follows:

A      B      C      Combined
-----------------------------------------------------------------------
11      22      3-1/4      11" x 22" x 3-1/4"
1.5      1-1/2      10      1.5" x 1-1/2" x 10"
1.5      NULL      10      1.5" x 10"
      NULL      10      10"

* The last row under A contains an empty string.

Adding a computed column as suggested by ScottPletcher is a good idea if you need to keep the Combined column up-to-date at all times. You'll still have to account for nulls and empty strings in your source columns though.

0
 
LVL 2

Expert Comment

by:floook
ID: 21747669
I forgot to trim the output string so:

UPDATE tableName
 SET Combined = RTIM( ... )

This will take care of case where column C (or B and C) is blank or null.
UPDATE tableName
 SET Combined = RTIM( ... )

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

808 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