Hello.
I need help with using CHARINDEX.
I have a column in a table (Discount_Specification) that could hold the
following values:
LF(I03U,CHA-14,ALL-0)
MR(I05U,I06U,CHA-5)
etc.
I'm inserting into another table and need to pick up the value following
"CHA-" in that column.
I've created a User Defined Function to do this but would like to make the
SQL code more efficient. The UDF has an argument which specifies which value
to pick up (separated by the delimiter). In example 1 it's the 2nd value. In
example 2 it's the 3rd value.
My SELECT code looks something like this:
select provider_id, last_name, first_name,
CASE
WHEN CHARINDEX(@.CHA,SM.Discount_Specification) > 0 THEN
CASE
WHEN Left(SM.Discount_Specification,2) IN(@.LF, @.LS, @.PF, @.PL) THEN
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue(We
bCentral.dbo.udf_GetNthTextValue
(SubString(SM. Discount_Specification,4,DataLength(RTri
m(SM.Discount_Specific
ation))-4),@.Comma,2),@.Dash,2),@.Comma,1))
ELSE
CASE
WHEN Left(SM.Discount_Specification,3) = @.CHA THEN
-- Charge is all by itself
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue
(RTrim(SM. Discount_Specification),@.Dash,2),@.Comma,
1))
ELSE 1
END
END
I would like to use a variable instead.
Something like:
DECLARE @.Pos SmallInt
SELECT provider_id, last_name, first_name,
@.Pos = CHARINDEX(@.CHA,SM.Discount_Specification)
CASE
WHEN @.POS > 0 THEN
webcentral.dbo.udf_ConvertDecimalAllowance etc. etc.
END
I get an error on the line where I'm setting @.Pos and I don't know what
syntax to use.
Any suggestions will be greatly appreciated.
Thanks,
RitaYou cannot set variables and return results to the client in the same SELECT
statement.
I'm not sure what it really is that you're trying to achieve, but I think
this function might help you parse those strings:
create function dbo.fnParse
(
@.charValue varchar(1000)
,@.findThis varchar(1000) = null
)
returns int
as
begin
declare @.result int
set @.findThis = isnull(@.findThis, 'CHA-')
select @.charValue
= substring(@.charValue, charindex(@.findThis, @.charValue) +
len(@.findThis), len(@.charValue))
select @.charValue
= substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
select @.result
= case
when isnumeric(@.charValue) = 1
then cast(@.charValue as int)
else null
end
return @.result
end
go
Use like this:
select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
ML
http://milambda.blogspot.com/|||Thanks so much for your response.
That's exactly what I'm playing around with - creating a UDF that uses the
CHARINDEX. Your example helps a lot!
Rita
"ML" wrote:
> You cannot set variables and return results to the client in the same SELE
CT
> statement.
> I'm not sure what it really is that you're trying to achieve, but I think
> this function might help you parse those strings:
> create function dbo.fnParse
> (
> @.charValue varchar(1000)
> ,@.findThis varchar(1000) = null
> )
> returns int
> as
> begin
> declare @.result int
> set @.findThis = isnull(@.findThis, 'CHA-')
> select @.charValue
> = substring(@.charValue, charindex(@.findThis, @.charValue) +
> len(@.findThis), len(@.charValue))
> select @.charValue
> = substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
> select @.result
> = case
> when isnumeric(@.charValue) = 1
> then cast(@.charValue as int)
> else null
> end
> return @.result
> end
> go
> Use like this:
> select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
> ,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
>
> ML
> --
> http://milambda.blogspot.com/|||Gee, that sounds like a good deed from me. Hope Santa reads this newsgroup.
:)
At least one of the elves should. Or is Santa not using SQL...?
Anyway, just remember that this *is* the newsgroup with solutions. :)
ML
http://milambda.blogspot.com/|||>> Any suggestions will be greatly appreciated. <<
1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
2) Learn to normalize your schema and stop writing COBOL-style string
manipulations in procedures. You will waste an insane amount of time
and spce doing this.
I also watched code like this kill some children in Africa. The
programmer had used strings hold the package size quantity for drugs.
When the drug suppliers agreed to provide smaller packages (i.e.
quantity one), the string was changed, but not the front end. The
result was when you thought you had ordered a 5-unit package, you got
a 1-unit package instead.
My guess is that you need a table of Discounts (notice the plural name
to show it is a set) with the amount, the source and the code for each
of the discounts. You then do a simple join and get rid of all that
"pseudo-COBOL" field extractions.|||Ouch!
I think I should have posted everything here pertaining to my question so
there would be no misunderstanding.
I have no control of the data coming in. We are supplied this by our clients
and then we have to import the non standard data into our standard SQL table
s.
No matter what, I can't get passed having to parse out bits of information
all strung
together within 1 column separated by a delimiter.
"--CELKO--" wrote:
> 1) Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> 2) Learn to normalize your schema and stop writing COBOL-style string
> manipulations in procedures. You will waste an insane amount of time
> and spce doing this.
> I also watched code like this kill some children in Africa. The
> programmer had used strings hold the package size quantity for drugs.
> When the drug suppliers agreed to provide smaller packages (i.e.
> quantity one), the string was changed, but not the front end. The
> result was when you thought you had ordered a 5-unit package, you got
> a 1-unit package instead.
> My guess is that you need a table of Discounts (notice the plural name
> to show it is a set) with the amount, the source and the code for each
> of the discounts. You then do a simple join and get rid of all that
> "pseudo-COBOL" field extractions.
>|||>> have no control of the data coming in. We are supplied this by our clien
ts and then we have to import the non standard data into our standard SQL ta
bles. No matter what, I can't get passed having to parse out bits of informa
tion all strung together wi
thin 1 column separated by a delimiter. <<
Just because the source data is a mess, you are not required to
propagate it in the schema. Parse it at load time and edit everything.
Have you looked into an ETL tool of some kind? You might be able to
write something in a small, fast scripting language likie AWK, Perl,
etc.|||Hmm.
I never thought to do that. The input files are always imported "as is" into
SQL staging tables using DTS packages. The reason being if there is ever a
question regarding the output data we have the original data in SQL format
against which queries can be run.
I use an ActiveX script to popultae the columns so that may be where I could
parse out the values. I've used AWK and Perl sparingly in the past. There is
a lot of logic going on when I get the value out of the string as to where t
o
place it so I think ActiveX is the best way to go.
I also need to consider speed since some of the files have millions of rows
in them.
Would it be faster to parse within the DTS ActiveX script or the Stored
Procedure (which I'm currently doing)? I heard that Stored Procedures are
much faster than DTS packages.
Thanks for the suggestion.
"--CELKO--" wrote:
within 1 column separated by a delimiter. <<
> Just because the source data is a mess, you are not required to
> propagate it in the schema. Parse it at load time and edit everything.
> Have you looked into an ETL tool of some kind? You might be able to
> write something in a small, fast scripting language likie AWK, Perl,
> etc.
>|||A set-based solution will be more efficient if run on the server. But that
will require accessing the source files through a linked server. So, the
question of the day is - what type are the source files?
ML
http://milambda.blogspot.com/|||They're fixed length text files.
"ML" wrote:
> A set-based solution will be more efficient if run on the server. But that
> will require accessing the source files through a linked server. So, the
> question of the day is - what type are the source files?
>
> ML
> --
> http://milambda.blogspot.com/
Veritek: