how to use created columns in SQL without showing these in the end result?-CodePudding

I have a long string from which I want to split columns using delimiter _

I know how to get the location using CHARINDEX and am going to use SUBSTRING() to get the correct information out. But to keep the code clean and avoid having to use a lot of nesting, I create columns which has the location of the delimiter using the code below. But now they show up in my end result as well. Is it possible to "store" these values without creating a whole column?

SELECT TOP 5
   CHARINDEX('_', SALESID, 0) AS first_lower,
   CHARINDEX('_', SALESID, first_lower 1) AS second_lower,

I want to avoid having to use nesting like:

SUBSTRING(SALESID, CHARINDEX('_', SALESID, CHARINDEX('_', SALESID, 0))-1, 1)

CodePudding user response：

This will only return MySubstringValue column in the end result.

SELECT
    SUBSTRING(...) As MySubstringValue
FROM
(
   SELECT TOP 5
      CHARINDEX('_', SALESID, 0) AS first_lower,
      CHARINDEX('_', SALESID, first_lower 1) AS second_lower
   FROM xxx
) tbl

CodePudding user response：

You could use CTE (Common Table Expressions) to structure complex queries and make them a bit more readable/manageable. Something like this:

;WITH CTE1 AS (SELECT CHARINDEX('_', SALESID, 0) AS first_lower, * FROM MyTable)
,CTE2 AS (SELECT CHARINDEX('_', SALESID, first_lower 1) AS second_lower, * FROM CTE1)
,CTE3 AS (SELECT SUBSTRING(SALESID, first_lower, second_lower - first_lower) AS calculated_column, * FROM CTE2)
SELECT SALESID, ...
FROM CTE3
WHERE calculated_column = 'qwerty'