How to write equations in the SELECT area

Question

I have 2 tables that I've Joined, One contains "Materials" and the other contains "Moldings". They are joined by the Material ID.

I want the end result to be a table of all of the unique materials being used.

The issue I'm running into is that the materials have lengths in the name. (eg. 10' Maple, 8' Maple) and I would like to exclude them when getting unique materials.

What I get now:

10' Maple
8' Maple
3' Maple
7' Cherry

What I want:

Maple
Cherry

What I have now is my attempt at getting rid of the numbers as it selects the table. Though my limited SQL knowledge is making it difficult.

SELECT LTrim(Right([CxMaterial].[Name],Len([CxMaterial].[Name])-5)) AS [EditedMaterial]
FROM [Molding] INNER JOIN
     [CxMaterial]
     ON [Molding].[Material ID] = [CxMaterial].[ID] 
GROUP BY [EditedMaterial]

The RIGHT and LEN are to get rid of the numbers The Trim is to trim the spaces (as some have 2 digits and some have 1)

The main errors are "you tried to execute a query that does not include the specified expression 'LTRIM(...-5))' as part of an aggregate function"

and a bunch of syntax errors when I attempt to fix that problem

Any help is appreciated Thank you

score 0 · Answer 1 · answered Jun 11 '19 at 20:57

0

I am assuming you are using SQL Server.

If you just want everything after the first space, you can use stuff() and charindex():

select stuff(str, 1, charindex(' ', str), '')
from (values ('7'' Cherry')) v(str)

answered Jun 11 '19 at 20:57

Gordon Linoff

1,242,037
58
646
786

gives me: Undefined function 'STUFF' in expression I'm making reports in Cabinet Vision, and they generate those based off of a database. I'm also assuming SQL Server, but I'm not sure if they play by their own rules or not. – Matt Jun 12 '19 at 18:31
@Matt . . . `STUFF()` is part of SQL Server, which is what your query looks like. However, you should be tagging your question with the database, as I've already asked for. – Gordon Linoff Jun 12 '19 at 19:52

score 0 · Answer 2 · answered Jun 11 '19 at 21:23

0

I'd use SUBSTRING() and CHARINDEX()

SELECT SUBSTRING([CxMaterial].[Name], CHARINDEX(' ',[CxMaterial].[Name]),[CxMaterial].[Name])) AS [EditedMaterial]
FROM [Molding] INNER JOIN
     [CxMaterial]
     ON [Molding].[Material ID] = [CxMaterial].[ID] 
GROUP BY [EditedMaterial]

answered Jun 11 '19 at 21:23

Peter

86
1
3

I did try that first, however, it didn't seem to like those functions. I'm using a program called Cabinet Vision. In it there are reports generated based on a database, so i'm not sure it follows all the same rules. I copy/pasted what you gave me and it's saying there is an Unknown Error :[ADODC]: Unknown error. [ADO]: – Matt Jun 12 '19 at 18:26

score 0 · Answer 3 · answered Jun 13 '19 at 15:24

Finally got it

SELECT (LTrim(Right([CxMaterial].[Name],Len([CxMaterial].[Name])-5)))

FROM ([Molding] INNER JOIN [CxMaterial] ON [Molding].[Material ID] = [CxMaterial].[ID])  

GROUP BY (LTrim(Right([CxMaterial].[Name],Len([CxMaterial].[Name])-5)))

It works, though I find it odd that it didn't before

How to write equations in the SELECT area

3 Answers3