[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
Medium Priority
145 Views
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
Question by:tupac1979

LVL 28

Assisted Solution

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

ID: 13833873
does the table have something like 55dd77;4mmm9999;
0

LVL 70

Expert Comment

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

LVL 5

Accepted Solution

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

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

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

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
Course of the Month18 days, 5 hours left to enroll