• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

What is the meaning of this TSQL syntax?

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.
2 Solutions
can you explain the question? where is the T-SQL code?
mpdillonAuthor Commented:
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

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 '.
Kevin CrossChief Technology OfficerCommented:
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!
mpdillonAuthor Commented:
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.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now