What is the meaning of this TSQL syntax?

Posted on 2011-10-12
Last Modified: 2012-05-12
I am reviewing someone else's TSQL. This is part of a View. What is the significance of the '(V)' and the '(N)'?
How is it different than just 'V' and 'N'?
Is there any significance to putting the V before the N?

This code extracts the 5 digit zip code from an address field, City, State zip.
Question by:mpdillon
    LVL 39

    Expert Comment

    can you explain the question? where is the T-SQL code?

    Author Comment

    I forgot to post the code. I can't believe it. So sorry.

                substring(ship_to_addr_3,charindex(' NV ', ship_to_addr_3) + 3,
                      len(ship_to_addr_3) - charindex(' NV ', ship_to_addr_3)),
          '(V)',''),'(N)',''))),1,5) as ZipCode

    LVL 13

    Accepted Solution

    It takes first 5 bytes after removing leading and trailing blanks from the substring of  ship_to_addr_3 starting first occurrence of ' NV ' after removing (V),(N)

    The order of (V) (N) does not matter. () do not have any syntactical significance. They just specify that we need to remove the string (V) and (N) from the the substring starting first occurrence of ' NV '.
    LVL 59

    Assisted Solution

    by:Kevin Cross
    Without seeing the actual data, it was probably just the original author's style or lack of understanding as the inner part on a string like '123 Mocking Bird Ln, Las Vegas NV 12345' would work simply as:

    SUBSTRING(ship_to_addr_3, CHARINDEX(' NV ', ship_to_addr_3)+4, 5)

    The REPLACE is unnecessary. If you do use it, you could also just REPLACE(..., ' NV ', '') so not sure why it was split apart as it was unless your data has '(V)' and '(N)' in it... REPLACE() does not do regular expression in SQL; therefore, the () are treated as part of the string. So really (V) and (N) don't do anything, since the +3 on the original code puts you past the ' NV'. Using +4 as I have puts you past the ' NV ' which eliminates the need to LTRIM(). Grabbing the next 5 characters avoids the longer version.

    If Zip Code is always last five characters, RIGHT(ship_to_addr_3, 5) is also a possibility.

    If you can have other characters after the Zip Code and can have other states other than NV, then this is also an option:

    SUBSTRING(ship_to_addr_3, PATINDEX('% [A-Z][A-Z] [0-9][0-9][0-9][0-9][0-9]%', ship_to_addr_3)+4, 5)

    Hope that helps!

    Author Closing Comment

    Thank you both. Especially for the extra explanation. I can't imagine there are any (V) or (N) in the data. But I will look. It was so odd to see those replaces and that is what prompted this question.
    I will be removing the replaces. The zip is stored either as a 5 digit zip  or a zip + 4 zip code.
    Thanks again. Now on to my next question regarding Stored procedures.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now