|
|
Posted by red vertigo on 01/17/07 08:39
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
Navigation:
[Reply to this message]
|