Does SQL have something like INSTR

Question

Post reply

Does SQL have something like INSTR

Alan Notton

Mr or Mrs. 500

Points: 594
More actions
July 15, 2005 at 7:44 am

#165000

I have a table where one of the columns has data input from an external source and cannot be altered, which leaves me with this problem.
The contents of this column is something like this:
PHD;TRC;YSH;YTRC (these are book codes)
I have a variable VAR which will contain something like YSH.
I want to create an SQL query which is able to check if the contents of VAR is contained within the column.
The command I'm after is something like ASP's INSTR, i.e.
If inStr(colName,VAR) Then count = 1
Obviously if there were only a couple of rows within the table I could use this type of command, but the table contains 250,000 rows!
Is there a way to solve this?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I don't have enought information to formualte an accurate answer. Can you post some sample data and the results needed?

Also you can look at the like operator and charindex('search', 'InThisString', StartPosition) > 0.

Sureshkumar Ramakrishnan Ten Centuries Points: 1200 More actions · Answer 2

alternatively u can use the substring function to get the strings

Kindest Regards,

Sureshkumar Ramakrishnan

Alan Notton Mr or Mrs. 500 Points: 594 More actions · Answer 3

Can this SUBSTRING function be used within a SELECT statement? And if so, would you mind supplying an example piece of code?

The table name is BookDetails

The column name is BIC

The argument variable name is VAR

SAMPLE DATA

| ISBN | BookTitle | BIC |

| 123456789 | Title #1 | PHD;TRC;YSH;YTRC |

| 232456789 | Title #2 | JK;JK1;JK2;JK3;JK4 |

| 534436789 | Title #3 | BHAJ |

Var can be equal to whatever the visitor selects, i.e. TRC, JK, JK3, YS, HA

So when the Select statement gets executed, the result will contain a lot less records for me to trawl through than the original 250,000.

When I get this new batch of results, I can then do a more thorough test on them. Basically, I want this INSTR or SUBSTRING command to shortlist the work that the thorough test needs to do.

If you've got some ideas, will you please type in some example code, so that I can be clear on syntax and format.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Do you want to select the records where the bic contains the choice of the user?

If you can't change the table design, this query will always take long, very long minutes to run (full table scan with string operation is not recommended on 250k rows...).

Select col1, col2 from dbo.BookDetails where ';' + BIC + ';' like '%;' + @VarName + '%;'

Alan Notton Mr or Mrs. 500 Points: 594 More actions · Answer 5

Alan Notton

Mr or Mrs. 500

Points: 594

July 15, 2005 at 8:49 am

#574871

Remi,

I'm looking into CHARINDEX now.

Alan

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

Remi - why not:

IF(SELECT CHARINDEX(@VarName, columnName)) > 1

do....

ELSE

do.....

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Can you create another table that keeps the same information vertically??

table BooksDetailsBIC

Bookid int

BIC varchar(5).

That way the query becomes increadibally more simple, an much faster :

Select Col1, Col2 from BooksDetails BD inner join NewTable NT on BT.Bookid and BD.Bookid and NT.BIC = @VarName

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

"TRC, JK, JK3, YS, HA"

If the use picks JK, all the books with JK and JK% will come back instead of just JK.

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

I'm not sure I understand the returning JK% part...

eg: If I did something like -

IF(SELECT CHARINDEX('JK2', 'TRC, JK, JK3, YS, HA')) > 1

print 'alright'

ELSE

print 'oh no'

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

IF(SELECT CHARINDEX('JK', 'TRC, JK3, YS, HA')) > 1

print 'alright'

ELSE

print 'oh no'

--"alright" when it should show "oh no" (JK is NOT in the list, bot JK3 is, that's why I added the bunch of concatenation with ';' to stop this behavior).

sushila SSC-Dedicated Points: 35293 More actions · Answer 11

thx. for explaining...NOW the concatenation makes perfect sense!

ps: should this be:

Select col1, col2 from dbo.BookDetails where ';' + BIC + ';' like ';%' + @VarName + '%;'

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Now you also now why we separate the list of items .

On a final note .

Declare @One as char(1)

SET @One = '1'

Select @One + @One as [Concatenation Makes Sens??]

--11

noeld SSC Guru Points: 96590 More actions · Answer 13

how about:

the concatention can also be used with chaindex:

IF(SELECT CHARINDEX( ','+'JK' +',' , ','+'TRC, JK3, YS, HA'+',')) > 1

print 'alright'

ELSE

print 'oh no'

and "oh no" is returned

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Ninja's_RGR'us

SSC Guru

Points: 294069

July 15, 2005 at 7:31 pm

#575085

I like like better... but that's just me .