Trim at a spicific character

[LHWC]

Hi,
I have a table with fields "TYPE" and "QTY". Typical values for "TYPE' are A, A_2MULL, A1_2MULL, B, etc. QTY are integers. I want to add the QTY for all records with the same characters to the left of "_". Example:
TYPE QTY
A 6
A_2MULL 4
A1_2MULL 5

RESULT:
TYPE QTY
A 10
A1 5
Thanks in advance.

 

[SQLBill]

Test this, you might have to 'play' with it to get data where there isn't a '_'.

SELECT SUBSTRING([Type],1, CHARINDEX('_', [Type])

-SQLBill

 

[TJRTech]

How about the follow...just change MyTable to your table name:

SELECT NEW_TYPE, SUM(QTY) as QTY
FROM
 (  SELECT 
       NEW_TYPE= CASE WHEN CHARINDEX('_', [TYPE])>0
                      THEN LEFT( [TYPE], 
                                 CHARINDEX('_',[TYPE])-1)
                      ELSE [TYPE]
                END,
       QTY
    FROM MyTable
 ) FOO
GROUP BY NEW_TYPE

 

[donutman]

TR, is it faster if you put the Case in the select and not use a derived table?
-Karl

 

[TJRTech]

Dunno, probably would be faster. I guess you could do it as long as the query engine allows group by of an aliased column computed via a case statement...which I guess it would.

The query would then be:

SELECT NEW_TYPE= CASE WHEN CHARINDEX('_', [TYPE])>0
                      THEN LEFT( [TYPE], 
                                 CHARINDEX('_',[TYPE])-1)
                      ELSE [TYPE]
                END,
       SUM(QTY) as QTY,
FROM MyTable
GROUP BY NEW_TYPE

LHWC, let us know which one is faster (use Execution Plan in Query Analyzer for both).

TR

 

[LHWC]

Thanks all!

TJR, I've tried both versions of your code, but I get an error on each. I'm using A2002, DAO. I created a new query in SQL view and entered the following:

SELECT NEW_TYPE=CASE WHEN CHARINDEX('_',[TYPE])>0
THEN LEFT([TYPE],
CHARINDEX('_',[TYPE])-1)
ELSE [TYPE]
END,
SUM(QTY) AS QTY,
FROM 222 CONN DH
GROUP BY NEW_TYPE;

I get a syntax error (missing operator)at WHEN.

For the alternative code:

SELECT NEW_TYPE, SUM(QTY) AS QTY
FROM
(SELECT NEW_TYPE=CASE WHEN CHARINDEX('_',[TYPE])>0
THEN LEFT([TYPE],
CHARINDEX('_',[TYPE])-1)
ELSE [TYPE]
END,
FROM 222 CONN DH)
FOO
GROUP BY NEW_TYPE;

I get the same message, although no code is highlighted.
What am I missing?

 

[TJRTech]

The following code is syntactically correct, I think:

SELECT NEW_TYPE = 
          CASE WHEN CHARINDEX('_',[TYPE])>0
               THEN LEFT([TYPE], CHARINDEX('_',[TYPE])-1)
               ELSE [TYPE]
          END,
       SUM(QTY) AS QTY
FROM 222 CONN DH
GROUP BY NEW_TYPE

I had an extra comma after QTY in the original post. Other than that, I think the above should be good to go.

 

[nabiS2K]

Are you running a local MS Access query? You need a Pass-through query pointing to a SQL Server Database for the suggested code to work.