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.
Thanks
pat
mpdillonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
can you explain the question? where is the T-SQL code?
0
mpdillonAuthor Commented:
I forgot to post the code. I can't believe it. So sorry.

substring(rtrim(ltrim(replace(replace(
            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

0
sameer2010Commented:
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 '.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
0
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.
pat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.