Results 1 to 6 of 6

Thread: search substring (same format) from one column

  1. #1
    Join Date
    Jan 2010
    Posts
    7

    search substring (same format) from one column

    I have one column which contain 6 character(post code), the first, third, fifth is letter and second, forth and sixth is number,how to get them(post code) from each string with sql query, thanks in advance.

    for example:
    we have address column which contain a long string like this:

    1111streetuniversityapp3h1p2w3montrealquebeccanada

    we need take h1p2w3 out, which is post code.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    I don't see a simple way to do this, as string before and after post code is of variable length.

    Also the pattern of alternate character and number can aslo occur in the string to make it nearly impossible to extract post code.

    I attempted this in SQL Server

    declare @str varchar(200)

    set @str='1111streetuniversityapp3h1p2w3montrealquebec canada'
    select substring(@str, patindex('%[a-z][0-9][a-z][0-9][a-z][0-9]%',@str),6)


    But the result I get is p3h1p2, which has same pattern as post code, but not what you are looking for.

  3. #3
    Join Date
    Jan 2010
    Posts
    7
    Skhanal ,thanks for your answer, yes, this is the most difficult part here, which is apppartment number just before the post code, so I am wondering sql query has enough function to do this job, or I should use VB or C to get the solution? if someone have advice, please let me know, it is urgent, thanks.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    If you have the logic to extract apt. number then you can do it in T-SQL. Otherwise you will run into same issue with VB as well. If it is always appx, that messes it up, then you can do this to get right code

    declare @str varchar(200)

    set @str='1111streetuniversityapp3h1p2w3montrealquebec canada'
    select substring(@str, patindex('%[^app_][a-z][0-9][a-z][0-9][a-z][0-9]%',@str),6)

    This still could not eliminate 3 after app.
    Last edited by skhanal; 01-26-2010 at 10:45 AM.

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Is it better to get table normalized?

  6. #6
    Join Date
    Sep 2005
    Posts
    168
    declare @str varchar(200)

    set @str='1111streetuniversityapp3h1p2w3montrealquebec canada'

    select reverse(substring(reverse(@str), patindex('%[0-9][a-z][0-9][a-z][0-9][a-z]%',reverse(@str)),6))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •