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(WebCentral.dbo.udf_GetNthTextValue        (SubString(SM.Discount_Specification,4,DataLength(RTrim(SM.Discount_Specification))-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,
Rita

You 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

Show quoteHide quote

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/

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 tables.
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.

Show quoteHide quote

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 to
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.

Show quoteHide quote

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.

Show quoteHide quote

Lookup "sp_addlinkedserver"  in Books Online and google for "schema.ini" (or
search in MSDN Online) to learn how to create a linked server to a text file
in order to query text files directly from SQL.

This way you can prepare all your source data before importing it into your
(normalized) database model.


ML

---
http://milambda.blogspot.com/

Many thanks for that piece of information.
I had no idea that it could be done.
Gotta love these forums!

Show quoteHide quote

This is most certainly *the* place to get answers. :)


ML

---
http://milambda.blogspot.com/

Default Value Calculation
Deadlock updating different rows
No retrieving dupicate rows
Service Broker - BEGIN DIALOG CONVERSATION @dialog_handle
Performance