[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query to search through string of values

Posted on 2005-04-21
6
Medium Priority
?
145 Views
Last Modified: 2010-03-19
I have column in table that contains values such as:
5D7;8M7;9M7
1H1

I would like to use a query that could get this values and then multiply them out for example
5D7 wouble 5 *7 + 8M7 would be 8 * 7 + 9M7 would be 9 * 7 = total and so on.
Can someone guide me in the right direction.
0
Comment
Question by:tupac1979
6 Comments
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 1200 total points
ID: 13833844
Try this:

DECLARE @SQL

SET @SQL = 'SELECT ' + REPLACE(REPLACE(REPLACE(YourColumn, ';', '+'), 'D', '*'), 'M', '*')
EXEC (@SQL)
0
 
LVL 8

Expert Comment

by:sankarbha
ID: 13833873
does the table have something like 55dd77;4mmm9999;
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13833883
Interesting.  I've never seen this but just off the top of my head I would have thought that M = * (multiply) and D = / (divide) instead of both being *.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 5

Accepted Solution

by:
obahat earned 800 total points
ID: 13833913

Here's a script that would do it.

DECLARE @Str VARCHAR(128)

IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
   DROP TABLE #tbl

CREATE TABLE #tbl (ColStr AS VARCHAR(128), ColVal DECIMAL(18,10)) -- or such

INSERT INTO #tbl
SELECT 'SELECT ' + REPLACE(REPLACE(REPLACE(YourColumn, ';', '+'), 'D', '*'), 'M', '*')

SELECT @Str = MIN(ColStr)
FROM #tbl
WHERE ColVal IS NULL

WHILE @Str IS NOT NULL
BEGIN
        EXEC('UPDATE #tbl
                 SET ColVal = ' + @Str + '
                 WHERE ColStr = ' + @Str)

       SET @Str = NULL

      SELECT @Str = MIN(ColStr)
      FROM #tbl
      WHERE ColVal IS NULL
END


There's gotta be a nicer, more recordset friendly way to do so, but I can't think of an immediate one...
0
 

Author Comment

by:tupac1979
ID: 13833954
no the main problem is first moving past the semicolon to get the next value to mulitply. And the D and M mean days and minutes but I am not concerned with converting them to time just only multiplying the values and moving past the semicolon if there is one. Thanks
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 1200 total points
ID: 13834528
Here's how to split the values separated by semi-colon:

DECLARE @Input VARCHAR(2000)
DECLARE @Formula VARCHAR(100)

WHILE LEN(@Input) > 0
BEGIN
    SET @Formula= LEFT ( @Input, ISNULL(NULLIF(CHARINDEX(';', @Input) - 1, -1), LEN@Input)))
    SET @Input = SUBSTRING( @Input, ISNULL(NULLIF(CHARINDEX(';', @Input), 0), LEN@Input)) + 1, LEN(@Input ))

    PRINT @Formula
END
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a 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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

830 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