Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Using a INSTR like function in SQL (500 points)

I need to run the following query (it worked in access, but access stinks), but I can not get it to work in SQL 2000 because SQL does not support an Instr() function.

The big thing is for me to check the values within the hA.MESSAGE field and return a certain value depending on its condition.  The hA.MESSAGE is very long, and I am trying to encapsulate some of the complexity from an XML dump.

I am open to creating a stored proc 1st and calling that, but I am not sure how to go about it
.

SELECT

Switch(     InStr(1,hA.MESSAGE,"archive",1),"Archive",
     InStr(1,hA.MESSAGE,"database tra",1),"Db Transfer",
     InStr(1,hA.MESSAGE,"backed",1),"Backup",
     InStr(1,hA.MESSAGE,"restore",1),"Restore") AS Type,
                   
     hA.numFailed, FormatDateTime(hA.DATE_TIME,2) & " " & FormatDateTime(hA.DATE_TIME,4) AS ATA,
     FormatDateTime(hA.startTime,2) & " " & FormatDateTime(hA.startTime,4) AS ATD,
     FormatDateTime(hA.scheduledStart,2) & " " & FormatDateTime(hA.scheduledStart,4) AS ETD,
     hA.numTransferSize,
     hA.NODE_NAME,
     hA.bytes

FROM hA;
0
makman111
Asked:
makman111
  • 6
  • 3
  • 2
  • +2
1 Solution
 
chaos_hooiCommented:
Not sure, this is what you want....

PATINDEX('character_pattern',character_expression)
- Returns the starting index value of the pattern.

InStr(1,hA.MESSAGE,"archive",1)
--> patindex('archive', hA.Message)
0
 
makman111Author Commented:
Do you know do preform the case statement?  Or the datetime conversion for that matter?

0
 
chaos_hooiCommented:
Well, I think, the examples in SQL Server Books Online will have better examples with 'case' statements... Just highlight 'case' in your query and press SHIFT-F1 at the same time. Choose T-SQL one and look at the example at the bottom.

As for datetime conversion, what kind?

declare @t datetime
set @t = '1/1/2003'
select convert(datetime,@t,101)

Highlight 'convert' and press SHIFT-F1 for more info.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ispalenyCommented:
You are looking for combination of CASE and LIKE
0
 
ispalenyCommented:
SELECT
CASE
WHEN MESSAGE LIKE "archive" then "Archive"
WHEN MESSAGE LIKE "database tra" then "Db Transfer"
WHEN MESSAGE LIKE "backed" then "Backup"
WHEN MESSAGE LIKE "restore" then "Restore"
END
AS Type
FROM hA
0
 
ispalenyCommented:
Corrected, % added

SELECT
CASE
WHEN MESSAGE LIKE "%archive%" then "Archive"
WHEN MESSAGE LIKE "%database tra%" then "Db Transfer"
WHEN MESSAGE LIKE "%backed%" then "Backup"
WHEN MESSAGE LIKE "%restore%" then "Restore"
END
AS Type
FROM hA
0
 
Eugene ZCommented:
(CHARINDEX and/or PATINDEX) and SUBSRTING (T-SQL)
<=>  Instr (VBA)
0
 
Scott PletcherSenior DBACommented:
You can do this with CHARINDEX which will be more efficient than LIKE or PATINDEX.  Also, I think the date formatting will match what you want.  If not, you will need to change the third parameter of the first CONVERT from 1 to whatever format you need:


SELECT CASE WHEN CHARINDEX('archive', hA.message) > 0 THEN 'Archive'
     WHEN CHARINDEX('database tra', hA.message) > 0 THEN 'Db Transfer'
     WHEN CHARINDEX('backed', hA.message) > 0 THEN 'Backup'
     WHEN CHARINDEX('restore', hA.message) > 0 THEN 'Restore'
     ELSE '' END AS Type,
     hA.numFailed,
     CONVERT(CHAR(8), hA.date_Time, 1) + ' ' + CONVERT(CHAR(5), hA.date_Time, 8),
     CONVERT(CHAR(8), hA.startTime, 1) + ' ' + CONVERT(CHAR(5), hA.startTime, 8),
     CONVERT(CHAR(8), hA.scheduledStart, 1) + ' ' + CONVERT(CHAR(5), hA.scheduledStart, 8),
    hA.numTransferSize,
     hA.NODE_NAME,
     hA.bytes
FROM hA;
0
 
ispalenyCommented:
ScottPletcher,
post your tests of efficiency of CHARINDEX, LIKE, PATINDEX for search inside string.
There can be difference in comparing algorithm, but in all cases the table index is full-scanned.
0
 
Scott PletcherSenior DBACommented:
Yes, a table scan is needed either way, but CHARINDEX doesn't have to interpret patterns and so is less complex and therefore runs faster.  I have personally seen this more than once in the past, although I don't have time right now to run tests to confirm it.  If you can post a significant test example (fairly long varchars with significant number of rows) that shows LIKE working as fast as CHARINDEX, I'll certainly be happy to evaluate it again.  Under no circumstances would I ever expect to see LIKE be faster than CHARINDEX.
0
 
ispalenyCommented:
TEST

--12210 x 8kChar rows generated in 20 s
set nocount on
create table #test(test varchar(8000))
begin tran
declare @Cnt int
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
insert #test(test) select len(replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70))
set @Cnt=1
WHILE @Cnt<=2 begin
 insert #test(test)
 select top 10000 replicate(newid(),70)+replicate(newid(),4)+replicate(newid(),70)+replicate(newid(),9)+replicate(newid(),70)
 from #test t1 cross join #test t2
 set @Cnt=@Cnt+1
END
commit tran
set nocount off


TEST 1 (RESTART SERVER,PREPARE AND RUN)
--Time/Duration/CPU/(All reads 183)
--8s/20/226
select count(*) from #test where test like '%test%'
--8s/50/236
select count(*) from #test where CHARINDEX('test', test) > 0
--9s/133/83
select count(*) from #test where PATINDEX('%test%', test) > 0

drop table #test

TEST 2 (PREPARE AND RUN 10 statements)
CHARINDEX:LIKE:PATINDEX=69:70:78


RESUMPTION
----------
I cannot decide if CHARINDEX is faster then LIKE, all I know PATINDEX is slower.
I noticed, that many experts at this site use PATINDEX when they needn't, probably they read some wrong book.

0
 
Scott PletcherSenior DBACommented:
Did you flush the data buffers between each run?
0
 
ispalenyCommented:
I restart the SQL server and create a new table !!!!!
But I posted all you need to perform your own tests.

Good luck!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now