Check format of field

Posted on 2011-03-09
Last Modified: 2012-08-14
Hi experts,

Is there a way I can check the format of the an nvarchar field? Meaning I want to make sure the text in this field is XX-XXXX, where X is a number 0-9.

Question by:MAVSS
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

ID: 35083550
Yes you can  used the constraints For this

Author Comment

ID: 35083587
Thanks, sarabhai but I should've clarified. I have to use a query as this will be displayed in a report.

Author Comment

ID: 35084944
Can the format of a field be checked using a SQL query?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 40

Expert Comment

ID: 35085044
Here - try this:

select * from tablename where columnname like '%[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%'
LVL 40

Accepted Solution

lcohan earned 500 total points
ID: 35085071
sorry you had XX-XXXX - updated below:

create table #t1 (c1 text)
insert #t1 values ('12-1234')
insert #t1 values ('ab-1234')
select * from #t1 where c1 like '%[0-9][0-9]-[0-9][0-9][0-9][0-9]%'

Author Closing Comment

ID: 35085829
Thanks. Used:
where c1 like '[0-9][0-9]-[0-9][0-9][0-9][0-9]' (Did not want to use %%)

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 63
SSRS Deployment problem 5 97
What can the "OPTION" in the GROUP help me in my query? 4 36
SQL Recursion schedule 13 35
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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