Create an sql variable from the query results to use in the same query

Question

I'd like to do the following query:

SELECT SUBSTRING(violations, CHARINDEX('X', violations, 0), 5) AS 'violations', definition, value, date 
FROM NewViolations WHERE CHARINDEX('X', violations, 0) > 0

However, I can't seem to figure out how to put CHARINDEX('X', violations, 0) into a variable (VarX) so that I end up with something like:

SELECT SUBSTRING(violations, VarX, 5) AS 'violations', definition, value, date
FROM NewViolations WHERE VarX > 0

I'm doing this to avoid running CHARINDEX twice.

David W · Accepted Answer · 2012-09-07 15:07:13Z

2

Try:

select substring(violations, location, 5) as 'violations', definition, value, date
 from (select violations, charindex('x',violations,0) location, definition, value, date
         from NewViolations) a
where a.location>0

For the sake of illustration and somewhat less typing, one could (although most would likely frown on it):

select substring(violations, location, 5) as 'violations', definition, value, date
 from (select *, charindex('x',violations,0) location
         from NewViolations) a
where a.location>0

edited Sep 7, 2012 at 15:07

answered Sep 7, 2012 at 15:00

David W

10.2k38 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Over a year ago

Sorry, the example I had wasn't complete. I also need a bunch of columns from the same table other than the substring one.

David W Over a year ago

Think you should be able to add them to the subselect and then pull them out into the main select.

Andrew Over a year ago

Why would your second example be frowned on?

David W Over a year ago

Well, select * constructs are generally not recommended because they return ALL the data from a table, so its preferable to select only the data that is needed. Reduces network traffic, exposes less information, things of that nature.

Joe Stefanelli · Accepted Answer · 2012-09-07 15:02:26Z

1

Assuming SQL Server 2005+, you could use a CTE:

WITH cteCharIndex AS (
    SELECT violations, CHARINDEX('X', violations, 0) AS PosOfX
        FROM NewViolations
)
SELECT SUBSTRING(violations, PosOfX, 5) AS 'violations'
    FROM cteCharIndex
    WHERE PosOfX > 0;

answered Sep 7, 2012 at 15:02

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Collectives™ on Stack Overflow

Create an sql variable from the query results to use in the same query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related