Search Text

Question

Post reply

Search Text

gboehm

Say Hey Kid

Points: 695
More actions
January 25, 2008 at 8:55 am

#182298

I'm trying to add a text search feature to a report. This first try will be to look for pre-defined text, in this case 'EMPTY'. Eventually, I need to allow the user to type in the search criteria. This code doesn't work - what do I need to do?
DECLARE @searchSQL AS VARCHAR(MAX)
DECLARE @SearchItem AS VARCHAR(MAX) ; SET @SearchItem = '%EMPTY%',
SELECT SQL where TAG = SearchItem,
SELECT substring (TAG , 10,50) as 'ALARM NAME',
Case when value=0 then 'RESET' else 'ON' end as 'STATUS',
CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'
FROM dbo.OMRON_1_ALARMS where TIME between @StartDate and @EndDate
The actual strings will be UNIT 1 EMPTY, UNIT 1 FULL and I only want to show the EMPTY data.
Thanks,
Gary

Viewing 7 posts - 1 through 6 (of 6 total)

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

gboehm Say Hey Kid Points: 695 More actions · Answer 1

After seaching more reference code, I now have this:

SELECT substring (TAG , 10,50) as 'ALARM NAME',

Case when value=0 then 'RESET' else 'ON' end as 'STATUS',

CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'

FROM dbo.OMRON_1_ALARMS where TAG like '%EMPTY%',

where TIME between @StartDate and @EndDate

Which works except I can't get the second where... statement to function. I've tried removing the , after the '%EMPTY%, I've tried a ; also. I just don't know how to have multiple statements.

Thanks,

Gary

gboehm Say Hey Kid Points: 695 More actions · Answer 2

Ignore these posts, this works:

SELECT substring (TAG , 10,50) as 'ALARM NAME',

Case when value=0 then 'RESET' else 'ON' end as 'STATUS',

CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'

FROM dbo.OMRON_1_ALARMS where TAG like '%EMPTY%' and TIME between @StartDate and @EndDate

Now to figure out how to all the user to enter the desired text to find.

'Feed 'em books and feed 'em books and all they do is eat 'em.'

Thanks,

Gary

gboehm Say Hey Kid Points: 695 More actions · Answer 3

Now I'm trying to allow the user to input the search text.

--DECLARE @enteralarm as VARCHAR(50), --; SET enteralarm = @AlarmName,

SELECT substring (TAG , 10,50) as 'ALARM NAME',

Case when value=0 then 'RESET' else 'ON' end as 'STATUS',

CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'

FROM dbo.OMRON_1_ALARMS where TAG like @EnterAlarm and TIME between @StartDate and @EndDate

As it is, the code runs, but doesn't find any EMPTY rows, which it should

If I use the DECLARE starement, it says I have defined @EnterAlarm twice.

Any help would be appreciated.

Gary

gboehm Say Hey Kid Points: 695 More actions · Answer 4

I'm real close now. This code works:

SELECT substring (TAG , 10,50) as 'ALARM NAME',

Case when value=0 then 'RESET' else 'ON' end as 'STATUS',

CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'

FROM dbo.OMRON_1_ALARMS where TAG like @EnterAlarm and TIME between @StartDate and @EndDate

But I have to enter the text as %EMPTY%.

What do I need to all entering the text without the %'s?

Thanks,

Gary

Chad Crawford SSChampion Points: 11610 More actions · Answer 5

Does this work for you:

where TAG like '%' + @EnterAlarm + '%'

Thanks,

Chad

gboehm Say Hey Kid Points: 695 More actions · Answer 6

Chad,

BINGO! That solved my problem! Thanks a million!

Gary