Query to search through string of values

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.
tupac1979Asked:
Who is Participating?
 
obahatCommented:

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
 
rafranciscoCommented:
Try this:

DECLARE @SQL

SET @SQL = 'SELECT ' + REPLACE(REPLACE(REPLACE(YourColumn, ';', '+'), 'D', '*'), 'M', '*')
EXEC (@SQL)
0
 
sankarbhaCommented:
does the table have something like 55dd77;4mmm9999;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
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
 
tupac1979Author Commented:
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
 
rafranciscoCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.