NVARCHAR Search Problem using LIKE

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • red vertigo
    #1

    NVARCHAR Search Problem using LIKE

    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_HARLegacyJo bSp(

    @JobNo NVARCHAR(10))

    AS


    SELECT IPJOBM_SID, --Start IPJOBM
    JOB_NUMBER_JBMS TR,
    CONTRACT_JOB_JB MSTR,
    RECORD_TYPE_JBM STR,
    ....
    TAX_EXEMPT_1_JB MSTR,
    TAX_EXEMPT_2_JB MSTR, --Finish IPJOBM
    PPJOBD_SID, --Start PPJOBD
    RELEASE_JOBDET,
    ....
    CATALOGUE_NUMBE R_JOBDET,
    ITEM_NUMBER_JOB DET,
    SHIPPED_COMPLET E_JOBDET,
    ISSUED_COMPLETE _JOBDET,
    QUANTITY_RETURN ED_JOBDET --Finish HARLegacy_PPJOB D

    FROM HARLegacy_IPJOB M
    JOIN HARLegacy_PPJOB D
    ON HARLegacy_PPJOB D.RELEASE_JOBDE T = HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR
    WHERE HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR 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

    Tags: None
    • othellomy@yahoo.com
      #2
      Re: NVARCHAR Search Problem using LIKE

      Hi,
      ALTER PROCEDURE Rpt_HARLegacyJo bSp(@JobNo NVARCHAR(10))
      AS
      select @jobNo =
      substring(@jobN o,patindex('%[1-9]%',@jobNo),data length(@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_HARLegacyJo bSp(
      >
      @JobNo NVARCHAR(10))
      >
      AS
      >
      >
      SELECT IPJOBM_SID, --Start IPJOBM
      JOB_NUMBER_JBMS TR,
      CONTRACT_JOB_JB MSTR,
      RECORD_TYPE_JBM STR,
      ...
      TAX_EXEMPT_1_JB MSTR,
      TAX_EXEMPT_2_JB MSTR, --Finish IPJOBM
      PPJOBD_SID, --Start PPJOBD
      RELEASE_JOBDET,
      ...
      CATALOGUE_NUMBE R_JOBDET,
      ITEM_NUMBER_JOB DET,
      SHIPPED_COMPLET E_JOBDET,
      ISSUED_COMPLETE _JOBDET,
      QUANTITY_RETURN ED_JOBDET --Finish HARLegacy_PPJOB D
      >
      FROM HARLegacy_IPJOB M
      JOIN HARLegacy_PPJOB D
      ON HARLegacy_PPJOB D.RELEASE_JOBDE T = HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR
      WHERE HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR 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

        Comment

        • othellomy@yahoo.com
          #3
          Re: NVARCHAR Search Problem using LIKE

          Hi,
          ALTER PROCEDURE Rpt_HARLegacyJo bSp(@JobNo NVARCHAR(10))
          AS
          select @jobNo =
          substring(@jobN o,patindex('%[^0]%',@jobNo),data length(@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_HARLegacyJo bSp(
          >
          @JobNo NVARCHAR(10))
          >
          AS
          >
          >
          SELECT IPJOBM_SID, --Start IPJOBM
          JOB_NUMBER_JBMS TR,
          CONTRACT_JOB_JB MSTR,
          RECORD_TYPE_JBM STR,
          ...
          TAX_EXEMPT_1_JB MSTR,
          TAX_EXEMPT_2_JB MSTR, --Finish IPJOBM
          PPJOBD_SID, --Start PPJOBD
          RELEASE_JOBDET,
          ...
          CATALOGUE_NUMBE R_JOBDET,
          ITEM_NUMBER_JOB DET,
          SHIPPED_COMPLET E_JOBDET,
          ISSUED_COMPLETE _JOBDET,
          QUANTITY_RETURN ED_JOBDET --Finish HARLegacy_PPJOB D
          >
          FROM HARLegacy_IPJOB M
          JOIN HARLegacy_PPJOB D
          ON HARLegacy_PPJOB D.RELEASE_JOBDE T = HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR
          WHERE HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR 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

            Comment

            • red vertigo
              #4
              Re: NVARCHAR Search Problem using LIKE

              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_HARLegacyJo bSp(@JobNo NVARCHAR(10))
              AS
              select @jobNo =
              substring(@jobN o,patindex('%[^0]%',@jobNo),data length(@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_HARLegacyJo bSp(

              @JobNo NVARCHAR(10))

              AS


              SELECT IPJOBM_SID, --Start IPJOBM
              JOB_NUMBER_JBMS TR,
              CONTRACT_JOB_JB MSTR,
              RECORD_TYPE_JBM STR,
              ...
              TAX_EXEMPT_1_JB MSTR,
              TAX_EXEMPT_2_JB MSTR, --Finish IPJOBM
              PPJOBD_SID, --Start PPJOBD
              RELEASE_JOBDET,
              ...
              CATALOGUE_NUMBE R_JOBDET,
              ITEM_NUMBER_JOB DET,
              SHIPPED_COMPLET E_JOBDET,
              ISSUED_COMPLETE _JOBDET,
              QUANTITY_RETURN ED_JOBDET --Finish HARLegacy_PPJOB D

              FROM HARLegacy_IPJOB M
              JOIN HARLegacy_PPJOB D
              ON HARLegacy_PPJOB D.RELEASE_JOBDE T = HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR
              WHERE HARLegacy_IPJOB M.JOB_NUMBER_JB MSTR 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

                Comment

                • Ed Murphy
                  #5
                  Re: NVARCHAR Search Problem using LIKE

                  red vertigo wrote:
                  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.
                  Is the 4 in 0014887 intentional?

                    Comment

                    Previous template Next
                    Working...