SQL查询中的有效记录
我有一个包含几列的表,其中一列是 DockNumber。如果它们确认为特定格式,我必须显示码头号码
前五个字符是数字,后跟 - 并后跟 5 个字符。最后一个字符应该是 alpha。
12345-678V9
如果前 5 个字符是数字并且有一个连字符,接下来的 3 个字符是数字,最后一个是字母,我如何在 SQL 中检查。
I have a table with few columns and one of the column is DockNumber. I have to display the docknumbers if they confirm to a particular format
First five characters are numbers followed by a - and followed by 5 characters. The last but one character should be a alpha.
12345-678V9
How can I check in SQL if the first 5 characters are numbers and there is a hyphen and next 3 are numbers and last but one is an alpha.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
基于@gbn的答案,这会检查以确保长度为11(如果@val不是char(11)或varchar(11),并且还检查以确保倒数第二个字符是alpha
Building on @gbn's answer, this checks to make sure the length is 11 (in case the @val is not a char(11) or varchar(11) and also checks to make sure the second to last char is alpha
你可以使用这个,你必须弄清楚如何使用它......
you can use this, you will have to figure it out on how to use this...
正则表达式可以成为您的朋友。
Regular Expressions can be your friend.
现在,这也允许小写 az。如果你只想要大写,你需要强制排序
Now, this allows lower case a-z too. You'd need to coerce collation if you wanted upper case only
PATINDEX 可能是理想的解决方案。
PATINDEX is probably the ideal solution.
应该可以,但我建议在代码中使用正则表达式。如果可能的话会容易得多。
should work, but i would suggest using Regular expression in code. Much easier if it is possible.
正则表达式应该是
'^\d{5}-\d{3}[AZ]\d$'
,因为没有^
和$
它将找到包含该序列的较长字符串 (122 12345-678V9 34)。The regex should be
'^\d{5}-\d{3}[A-Z]\d$'
, because without^
and$
it would find longer strings that contain that sequence (122 12345-678V9 34).使用规则
然后将规则绑定到列
Use rule
Then bind rule to column