Query to match a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query to match a string

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-10-14 : 16:36:32

Hi everyone -

I have a query that needs to look into a
string value for a series of characters....

Here is the query...

select adt_lkey from tbladt where adt_dtdischarge is null and adt_szpatienttype like (select adtc_szDischargeTypes from tbladtconfig)

the value returned in the sub-query
select adtc_szDischargeTypes from tbladtconfig
looks like this....
'OP,ER,IP'

but the value in the field from the primary query is 'OP'

I was thinking something using a like,
but i'm not sure on how to write the query

any help would be appreciated

thanks
tony

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-14 : 17:12:39

Any chance of a table redesign?

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-14 : 17:46:24

Could get around it using a CSV Parsing function similar to this:

create function dbo.fn_chop_csv
(
@CSVString 	varchar(8000) ,
@Delimiter	varchar(10)
)
returns @tbl table (s varchar(1000))
as
begin
declare @i int ,
	@j int
select 	@i = 1,
	@CSVString = @CSVString + @Delimiter

	while @i < len(@CSVString)
	begin
		select	@j = charindex(@Delimiter, @CSVString, @i)
		if @j = 0
		begin
			select	@j = len(@CSVString) + 1
		end
		insert	@tbl select substring(@CSVString, @i, @j - @i)
		select	@i = @j + len(@Delimiter)
	end
	return
end

Then perform your query like this:

declare @csv varchar(100)
select @csv = adtc_szDischargeTypes 
from tbladtconfig
where tablePK = 1 -- or whatever

select adt_lkey 
from tbladt 
where adt_dtdischarge is null 
	and adt_szpatienttype in (select * from dbo.fn_chop_csv(@csv, ','))

Do a search on SQLTeam for CSV... youll find examples.

Nathan Skerl

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-10-17 : 11:05:35

Hi Nathan -

thanks for the code....

however, it fails at
returns @tbl table (s varchar(1000))

ASA Error -131: Syntax error near '@tbl'

any ideas???

thanks
tony

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-17 : 11:15:56

Are you using MS SQL Server?

Nathan Skerl

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-10-17 : 13:55:15

Nope - ASA 7

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-17 : 14:04:16

Yea, sorry about that. The code I posted is for SQL Server, Im not sure how ASA deals with User Defined Functions.

As SQLTeam is primarily a MSSQL Server forum you might find more detailed help at an ASA/Sybase forum.

Nathan Skerl

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-10-17 : 15:50:55

Hi Nathan and everyone -

Thanks for the code and replies,
yes it is a ASA (Sybase) database,
and the problem is passing back a temp table -

no one can seem to answer that on the sybase forums

so we'll see what happens

thanks again

r937
Posting Yak Master

112 Posts

Posted - 2005-10-17 : 19:32:47

try this --

select adt_lkey 
  from tbladt 
inner
  join tbladtconfig  
 where adt_dtdischarge is null 
   and ','+adtc_szDischargeTypes+',' like '%,'+adt_szpatienttype+',%'

when evaluated, the WHERE condition will look like this --

... and ',OP,ER,IP,' like '%,OP,%'

by appending commas front and back, this allows the LIKE term to match both the front and rear of the string

rudy
http://r937.com/

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-18 : 00:31:37

Hey Rudy!
I was hoping someone would jump in with the pre and post-pended commas.
But I'd do this using a charindex or patindex rather than LIKE. Wonder if there is a performance difference?

pithhelmet
Posting Yak Master

183 Posts

Posted - 2005-10-20 : 12:24:35

Good point Rudy -

This solution will certainly eliminate a temp table,
and that in itself will make things a heck of a lot faster!

i'll implement today

thanks
take care
tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources