November 30, 2015 at 12:02 pm
Hi all,
I am trying to split (incorrect) cellcontents into smaller pieces.
If I use:
SELECT cdsDescription
, CHARINDEX(' ',cdsDescription) AS [1stPos]
, CHARINDEX(' ', cdsDescription, CHARINDEX(' ',cdsDescription) + 1) AS [2ndPos]
FROM tbCompdistances
the code works fine.
If I try to simplify the code (which will grow) for easier reading, and try to use a variable SQL Server states that "cdsDescription" is an invalid column name.
The code I use:
DECLARE @FirstBlankPosition INTEGER
SET @FirstBlankPosition = CHARINDEX(' ', [cdsDescription], 1)
Is it possible to do what I want, or is the code invalid.
Thanks in advance
Hein
November 30, 2015 at 12:10 pm
you are missing the FROM clause. you can't select a column name without it's table.
SpeedSkaterFan (11/30/2015)
Hi all,I am trying to split (incorrect) cellcontents into smaller pieces.
If I use:
SELECT cdsDescription
, CHARINDEX(' ',cdsDescription) AS [1stPos]
, CHARINDEX(' ', cdsDescription, CHARINDEX(' ',cdsDescription) + 1) AS [2ndPos]
[highlight="#ffff11"]FROM tbCompdistances[/highlight]
the code works fine.
If I try to simplify the code (which will grow) for easier reading, and try to use a variable SQL Server states that "cdsDescription" is an invalid column name.
The code I use:
DECLARE @FirstBlankPosition INTEGER
SET @FirstBlankPosition = CHARINDEX(' ', [cdsDescription], 1)
[highlight="#ffff11"]FROM tbCompdistances[/highlight]
Is it possible to do what I want, or is the code invalid.
Thanks in advance
Hein
Lowell
November 30, 2015 at 12:27 pm
Hi Lowell,
Thanks for your reply
If I change the code to (and add a select):
DECLARE @FirstBlankPosition INTEGER
SET @FirstBlankPosition = (SELECT CHARINDEX(' ', [cdsDescription], 1) FROM tbCompdistances)
SELECT cdsDescription, @FirstBlankPosition
FROM tbCompdistances
I get the error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Also all results are set to NULL
I guess the "=" right behind the @FirstBlankPosition is causing this problem
Is there an alternative way to declare @FirstBlankPosition?
Hein
November 30, 2015 at 8:27 pm
There are a few ways to do exactly what you want to do, and I will show you at the end of this post. But what you want to do is probably not what you really want to do. 😉
You wrote that you wanted to "simplify" the code by using a variable. Does this mean you intend to use a cursor or a while loop as well? After all, in the first query you are performing a set based operation: "take every value in the cdsDescription column of a table, and apply an expression to it". But in the "simplification" you can only ever be working with one particular cdsDescription, since @FirstBlankPosition is a scalar variable, and a scalar variable cannot contain more than one value. So if you want the "simplified" code to do the same thing as the original code, you would have to run it many times (as many times as there are rows in the tbCompdistances table).
In other words, your first code example is ultimately simpler... and much faster as well.
The only time you might want to use the "variable" version (although I still wouldn't) is if you are absolutely certain that you will only ever be working with a single row. Maybe the tbCompDistances table can only ever contain one row (but my intuition is that this is unlikely given the name of the table).
Anyway, as promised, here's some code for you:
declare @t table (i int)
insert @t select 1 union all select 2
declare @i int
-- this won't work, since "set" expects a scalar value
set @i = (select i from @t)
-- this will "work" (in that it will execute without error).
-- but the @i variable can still only contain a single value. Will the value be 1 or 2? How do you know?
select @i = i from @t
-- this will work. The "top 1" means the engine knows that only a single row will be returned
-- plus, we have told it what we want to order by, so we know that the @i variable will be 1 after this execution
-- But as I said above, you PROBABLY DON'T WANT TO DO THIS.
set @i = (select top 1 i from @t order by i)
December 1, 2015 at 12:30 pm
Hi Don,
Thanks for your reply.
What I am trying to do:
the field cdsDescription is filled automatically, using code I cannot reach since it is made by somebody else and which is unreachable.
Most of the times the code works OK.
On some occasions it doesn't.
To find out in which case the code makes a mistake (in a table with ± 300.000 records and growing) I want to identify the first and second "word/number"
Text can eg be: "500m 1500 boys 14 years old", or "1000 1000 mtr Ladies senior". (I know, stupid descriptions but I didn't write the code).
My goal was to make a calculation where the first and second blank space in this description is. Preferably with something like:
DECLARE @FirstBlank AS INT
SET @FirstBlank = CharIndex(... etc)
This @FirstBlank I wanted to use in the remaining code, and avoid things like (as part of a Case When, so the code is much longer):
... SUBSTRING(cdsDescription, CHARINDEX(' ', cdsDescription) + 1, LEN(cdsDescription) - CHARINDEX(' ', cdsDescription) + 1) ...
This afternoon I did as you suggested and just made the code, including the long formulas.
This works fine, after some trial and error to get the correct syntac.
My thoughts about simplyfying were to ambitious (at least for me)
Sometimes something looks simpler than it actually is.
Anyway: thanks for your reply.
Hein
PS
I see you are a motorcycle fan. Is that an Yamaha Diversion?
December 1, 2015 at 12:40 pm
this is syntactically correct,, and gives you your start and end indicators, as a set base doperation.
is this more like what you are after?
SELECT cdsDescription,st.[1stPos],en.[2ndPos]
FROM tbCompdistances
CROSS APPLY(SELECT CHARINDEX(' ',cdsDescription) AS [1stPos]) st
CROSS APPLY(SELECT CHARINDEX(' ', cdsDescription, CHARINDEX(' ',cdsDescription) + 1) AS [2ndPos]) en
Lowell
December 1, 2015 at 12:57 pm
Hi Lowell,
That is MUCH easier than the code I came up with, thanks.
1 question: if I want to join other tables to tbCompDistances, should I do that right behind / after "from tbCompDistances" or should I do that after the 2 cross applies?
I am not familiair with Cross Apply so I have to look into that
I thought that Cross Apply was used with subqueries, and saw no use for it here (mostly due to not knowing how to use it)
Thanks
Hein
December 1, 2015 at 2:12 pm
for me, mentally,
CROSS APPLY = INNER JOIN
and
OUTER APPLY = LEFT OUTER JOIN
so if anything is going to consume the results of the previous JOIN/CROSS (ie using the st.[1stPos], or a substring based on it, they have to exist under it's declarations, otherwise they are fine above.
Lowell
December 1, 2015 at 5:03 pm
SpeedSkaterFan (12/1/2015)
I see you are a motorcycle fan. Is that an Yamaha Diversion?
That's at Eastern Creek (Sydney Australia) on my old Honda VTR1000, circa 2005. I think I lowsided shortly after this photo, only crash I've ever had :Whistling:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply