Creating a calculated boolean colum using CharIndex results

Question

Post reply

Creating a calculated boolean colum using CharIndex results

j2associates

SSC Veteran

Points: 277
More actions
April 13, 2007 at 7:03 am

#119553

Hello All,
I want to create a calculated IsValue bit column in a select statement using CharIndex > 0. I can do this just fine: CharIndex('SearchValue', ColumnName). But when I try to convert it to a boolean based upon the above being greater than zero, I get an Incorrect syntax near '>'. error regardless of whether I use Cast or Convert.
I get the error when I try something like: Cast((CharIndex('SearchValue', ColumnName) > 0) As bit). Any ideas and/or suggestions would be greatly appreciated!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

SQL Server will convert the value for you!

Only zero value will be "BIT" zero. All other values (1, -1, 245, 40000) evaluates as "BIT" 1.

N 56°04'39.16"
E 12°55'05.25"

j2associates SSC Veteran Points: 277 More actions · Answer 2

Hello Peter,

Thanks for responding! It appears that I did not communicate clearly. If I just use the CharIndex function, I get values like 0, 5, 8, 4, etc. But I want to sort on my calculated column and then by column name and the numeric values throw off the column name sort. That's why I need to convert it to a boolean column so it returns either 0 or 1. When I try to test for CharIndex > 0 I get the error. Thanks again for taking time to respond!

Ken McKelvey SSCoach Points: 18844 More actions · Answer 3

Why do you want to do a comparison?

DECLARE @b-2 bit

SET @b-2 = CHARINDEX('c', 'abc')

SELECT @b-2

SET @b-2 = CHARINDEX('d', 'abc')

SELECT @b-2

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

I thought I was crystal clear, super green, in my answer.

N 56°04'39.16"
E 12°55'05.25"

Ken McKelvey SSCoach Points: 18844 More actions · Answer 5

Peter,

Sorry for not being clear.

I was refering to Gary's charindex > 0 test.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Don't apologize. You and me are onthe same track.

We only have to convince Gary.

N 56°04'39.16"
E 12°55'05.25"

j2associates SSC Veteran Points: 277 More actions · Answer 7

Hello,

I have a multiple condition where clause where condition 2 is to select data if the Descriptioni column data values contain the string 'Offset'. Description data values might be OffsetDistance, HorizonalOffset, VerticalOffset, OffsetType, LateralOffset, etc. Condition2 selects a handful of additional records that fail Condition1.

The where clause is as follows and selects correctly:

Where Condition1 Or CharIndex('Offset', Description) > 0

I want to sort by Condition2 Descending so the extra rows selected by Condition2 are first but I want them to be sorted by Description value. So in the examples above OffsetDistance is 1, HorizontalOffset is 11, etc. But I want them to sort by name so HorizontalOffset, LateralOffset, OffsetDistance, VerticalOffset, etc.

This Select statement and Order By statement works

Select CharIndex('Offset', Description) As Offset From etc Where etc Order By Offset

This Select statement and Order By statement fails with the error noted above

Select (CharIndex('Offset', Description) > 0) As IsOffset From etc Where etc Order By IsOffset

I appreciate you guys sticking with me on this. It's not huge but it bugs me that I can't get it to work the way I want it to work.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Select CASE WHEN CharIndex('Offset', Description) > 0 THEN 1 ELSE 0 END As IsOffset From etc Where etc Order By IsOffset

N 56°04'39.16"
E 12°55'05.25"

j2associates SSC Veteran Points: 277 More actions · Answer 9

Thanks Peter! Kind of an embarrassing duh brain cramp on my part. I appreciate you sticking with the thread, your solution works like a charm!