How to remove Characters from Alphanumeric string?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: How to remove Characters from Alphanumeric string?

  1. #1
    Peishan Guest

    How to remove Characters from Alphanumeric string?


    Hi

    I have a field which contains strings that are made up of Numeric and Characters
    at varied positions. Is there a way to remove all characters within each
    string, or a way of specifying all the target characters to be replaced with
    a space in one query
    e.g. for '$20M-50E' to become '20-50'

    I need to run the code in SQL Server.

    Many Thanks
    Peishan


  2. #2
    Matthew mark Guest

    Re: How to remove Characters from Alphanumeric string?


    "Peishan" <PeishanM@gfiholdings.co.uk> wrote:
    >
    >Hi
    >
    >I have a field which contains strings that are made up of Numeric and Characters
    >at varied positions. Is there a way to remove all characters within each
    >string, or a way of specifying all the target characters to be replaced

    with
    >a space in one query
    >e.g. for '$20M-50E' to become '20-50'
    >
    >I need to run the code in SQL Server.
    >
    >Many Thanks
    >Peishan
    >


    Depending on what version of sql server you are using. I am using 7.0 and
    it does not support user-defined functions. However, it does let you create
    procedures with Input/output variables.

    Better yet, if you have SQL server 2000, it does let you write your own functions,
    so you could use the function on a specific column in a table. In lieu of
    this, I think you could use two procedures, with the final one displaying
    a select statement witht the end result. You'd be using the charindex function
    and the substring function.

    The best solution might be to create a separate field in the table, run the
    process and put the new result in the table then run your select statement
    using the new value.

    Does this make sense?

  3. #3
    Peishan Guest

    Re: How to remove Characters from Alphanumeric string?


    "Matthew mark" <matthew.mark@edwards.af.mil> wrote:
    >
    >"Peishan" <PeishanM@gfiholdings.co.uk> wrote:
    >>
    >>Hi
    >>
    >>I have a field which contains strings that are made up of Numeric and Characters
    >>at varied positions. Is there a way to remove all characters within each
    >>string, or a way of specifying all the target characters to be replaced

    >with
    >>a space in one query
    >>e.g. for '$20M-50E' to become '20-50'
    >>
    >>I need to run the code in SQL Server.
    >>
    >>Many Thanks
    >>Peishan
    >>

    >
    >Depending on what version of sql server you are using. I am using 7.0 and
    >it does not support user-defined functions. However, it does let you create
    >procedures with Input/output variables.
    >
    >Better yet, if you have SQL server 2000, it does let you write your own

    functions,
    >so you could use the function on a specific column in a table. In lieu of
    >this, I think you could use two procedures, with the final one displaying
    >a select statement witht the end result. You'd be using the charindex function
    >and the substring function.
    >
    >The best solution might be to create a separate field in the table, run

    the
    >process and put the new result in the table then run your select statement
    >using the new value.
    >
    >Does this make sense?


    Hi Matthew

    thanks for your suggestions. Same as you I'm using SQL Server 7.0.

    The Replace([fieldName], 'find string', 'replace with') function wrapped
    in Trim() would solve the basic problem. It allows me to specify indivdually
    each characters I want to replace.

    However, the next problem is that I would need to set up a large number of
    queries to replace all the possible characters I have in the column.

    Translate() for Oracle would work wonders as it lets you specify all the
    different characters you want to replace in one single query. I don't suppose
    there's an equivalent function or combinations of function in SQL server
    which would work similarly??




  4. #4
    Bud Dean Guest

    Re: How to remove Characters from Alphanumeric string?

    can you check for a range of ascii numbers?
    "Peishan" <peishanm@gfiholdings.co.uk> wrote in message
    news:3cbff603$1@10.1.10.29...
    >
    > "Matthew mark" <matthew.mark@edwards.af.mil> wrote:
    > >
    > >"Peishan" <PeishanM@gfiholdings.co.uk> wrote:
    > >>
    > >>Hi
    > >>
    > >>I have a field which contains strings that are made up of Numeric and

    Characters
    > >>at varied positions. Is there a way to remove all characters within each
    > >>string, or a way of specifying all the target characters to be replaced

    > >with
    > >>a space in one query
    > >>e.g. for '$20M-50E' to become '20-50'
    > >>
    > >>I need to run the code in SQL Server.
    > >>
    > >>Many Thanks
    > >>Peishan
    > >>

    > >
    > >Depending on what version of sql server you are using. I am using 7.0 and
    > >it does not support user-defined functions. However, it does let you

    create
    > >procedures with Input/output variables.
    > >
    > >Better yet, if you have SQL server 2000, it does let you write your own

    > functions,
    > >so you could use the function on a specific column in a table. In lieu of
    > >this, I think you could use two procedures, with the final one displaying
    > >a select statement witht the end result. You'd be using the charindex

    function
    > >and the substring function.
    > >
    > >The best solution might be to create a separate field in the table, run

    > the
    > >process and put the new result in the table then run your select

    statement
    > >using the new value.
    > >
    > >Does this make sense?

    >
    > Hi Matthew
    >
    > thanks for your suggestions. Same as you I'm using SQL Server 7.0.
    >
    > The Replace([fieldName], 'find string', 'replace with') function wrapped
    > in Trim() would solve the basic problem. It allows me to specify

    indivdually
    > each characters I want to replace.
    >
    > However, the next problem is that I would need to set up a large number of
    > queries to replace all the possible characters I have in the column.
    >
    > Translate() for Oracle would work wonders as it lets you specify all the
    > different characters you want to replace in one single query. I don't

    suppose
    > there's an equivalent function or combinations of function in SQL server
    > which would work similarly??
    >
    >
    >




  5. #5
    Peishan Guest

    Re: How to remove Characters from Alphanumeric string?


    I could check for a range of ascii numbers, but is there a way i can do so
    in one single execution in SQL server 7.0?

    "Bud Dean" <bud_dean@hotmail.com> wrote:
    >can you check for a range of ascii numbers?
    >"Peishan" <peishanm@gfiholdings.co.uk> wrote in message
    >news:3cbff603$1@10.1.10.29...
    >>
    >> "Matthew mark" <matthew.mark@edwards.af.mil> wrote:
    >> >
    >> >"Peishan" <PeishanM@gfiholdings.co.uk> wrote:
    >> >>
    >> >>Hi
    >> >>
    >> >>I have a field which contains strings that are made up of Numeric and

    >Characters
    >> >>at varied positions. Is there a way to remove all characters within

    each
    >> >>string, or a way of specifying all the target characters to be replaced
    >> >with
    >> >>a space in one query
    >> >>e.g. for '$20M-50E' to become '20-50'
    >> >>
    >> >>I need to run the code in SQL Server.
    >> >>
    >> >>Many Thanks
    >> >>Peishan
    >> >>
    >> >
    >> >Depending on what version of sql server you are using. I am using 7.0

    and
    >> >it does not support user-defined functions. However, it does let you

    >create
    >> >procedures with Input/output variables.
    >> >
    >> >Better yet, if you have SQL server 2000, it does let you write your own

    >> functions,
    >> >so you could use the function on a specific column in a table. In lieu

    of
    >> >this, I think you could use two procedures, with the final one displaying
    >> >a select statement witht the end result. You'd be using the charindex

    >function
    >> >and the substring function.
    >> >
    >> >The best solution might be to create a separate field in the table, run

    >> the
    >> >process and put the new result in the table then run your select

    >statement
    >> >using the new value.
    >> >
    >> >Does this make sense?

    >>
    >> Hi Matthew
    >>
    >> thanks for your suggestions. Same as you I'm using SQL Server 7.0.
    >>
    >> The Replace([fieldName], 'find string', 'replace with') function wrapped
    >> in Trim() would solve the basic problem. It allows me to specify

    >indivdually
    >> each characters I want to replace.
    >>
    >> However, the next problem is that I would need to set up a large number

    of
    >> queries to replace all the possible characters I have in the column.
    >>
    >> Translate() for Oracle would work wonders as it lets you specify all the
    >> different characters you want to replace in one single query. I don't

    >suppose
    >> there's an equivalent function or combinations of function in SQL server
    >> which would work similarly??
    >>
    >>
    >>

    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center