Posted by red vertigo on 01/17/07 15:58

Hi othellomy,
Thankyou very much indeed for replying to my post,

Interesting, haven't seen patindex() used in this way before. Will take
this information forward and hopefully come up with the solution to
this particular problem,

Appreciated:)

rohan




othellomy@yahoo.com wrote:
> Hi,
> ALTER PROCEDURE Rpt_HARLegacyJobSp(@JobNo NVARCHAR(10))
> AS
> select @jobNo =
> substring(@jobNo,patindex('%[^0]%',@jobNo),datalength(@jobNo))
>
> might give you jobNo with leading zero removed. However, I would
> recommend not supplying parameter with leading zero added if possible
> (therefore you wont need this code).
>
>
>
> red vertigo wrote:
> > HI All,
> > Can't quite get my head aorund this problem. My knowledge or more
> > importantly my experience of using T-SQL is small and as a consequence
> > my stored procedures are basic.
> >
> > My current project involves simple stored_procs searching/retrieving
> > Legacy data. Key problem is that in the case of the 'Job' table, the
> > 'Job Number' is an alphanumeric value. Here is the current stored
> > procedure:
> >
> > ALTER PROCEDURE Rpt_HARLegacyJobSp(
> >
> > @JobNo NVARCHAR(10))
> >
> > AS
> >
> >
> > SELECT IPJOBM_SID, --Start IPJOBM
> > JOB_NUMBER_JBMSTR,
> > CONTRACT_JOB_JBMSTR,
> > RECORD_TYPE_JBMSTR,
> > ...
> > TAX_EXEMPT_1_JBMSTR,
> > TAX_EXEMPT_2_JBMSTR, --Finish IPJOBM
> > PPJOBD_SID, --Start PPJOBD
> > RELEASE_JOBDET,
> > ...
> > CATALOGUE_NUMBER_JOBDET,
> > ITEM_NUMBER_JOBDET,
> > SHIPPED_COMPLETE_JOBDET,
> > ISSUED_COMPLETE_JOBDET,
> > QUANTITY_RETURNED_JOBDET --Finish HARLegacy_PPJOBD
> >
> > FROM HARLegacy_IPJOBM
> > JOIN HARLegacy_PPJOBD
> > ON HARLegacy_PPJOBD.RELEASE_JOBDET = HARLegacy_IPJOBM.JOB_NUMBER_JBMSTR
> > WHERE HARLegacy_IPJOBM.JOB_NUMBER_JBMSTR LIKE '%' + @JobNo + '%'
> >
> > Job Number values in the table can either begin with a Letter e.g.
> > 'H3887' or a Number '13016'
> >
> > The problem here is that when a JobNumber value e.g. '001887' with
> > a/more than one proceeding zero(s) is passed to the stored procedure no
> > value is returned. I am assuming this to mean that the current
> > WHERE....LIKE statement interprets this example value as '0'? As there
> > are no '0' values then no value is returned?
> >
> > I have read about the SUBSTRING function in Books Online which I kind
> > of understand but do not exactly know if this functions is suitable for
> > this problem?
> >
> > What I want the stored procedure to do when executing is
> >
> > 1) Read in the value of @JobNo and identify n number of leading zeros
> > (according to the Legacy data this can be 1 or more) . If the JobNumber
> > begins with an alphabetic e.g. H3887 then it will just search and
> > return all 'H' values.
> > 2) Perform search on/from first whole number e.g. '1887'
> >
> > This means that as I am using the LIKE command it would be acceptable
> > for this search to produce variations on '001887' e.g. 0014887 etc.
> >
> > Please can anyone provide me with some guidance on how I can achieve
> > this, which at first glance, seems a baffling feature?!?
> >
> > Any advice would be greatly appreciated,
> >
> > Many Thanks
> >
> > rohan

[Back to original message]