Posted by othellomy on 01/17/07 11:15

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]