?
Solved

Using a INSTR like function in SQL (500 points)

Posted on 2003-03-11
13
Medium Priority
?
254 Views
Last Modified: 2008-01-16
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
Comment
Question by:makman111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 4

Expert Comment

by:chaos_hooi
ID: 8117438
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
 

Author Comment

by:makman111
ID: 8117838
Do you know do preform the case statement?  Or the datetime conversion for that matter?

0
 
LVL 4

Expert Comment

by:chaos_hooi
ID: 8117922
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 13

Expert Comment

by:ispaleny
ID: 8118147
You are looking for combination of CASE and LIKE
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8118167
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 8118182
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8118914
(CHARINDEX and/or PATINDEX) and SUBSRTING (T-SQL)
<=>  Instr (VBA)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8120099
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 8123107
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8123329
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 8124198
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8127916
Did you flush the data buffers between each run?
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 1000 total points
ID: 8128277
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question