Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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

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
nuvium
Asked:
nuvium
1 Solution
 
nuviumAuthor Commented:
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
 
elimesikaCommented:
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
 
Scott PletcherSenior DBACommented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
scgstuffCommented:
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
 
floookCommented:
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
 
floookCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now