检查sql中的字符串格式 [英] check string format in sql
本文介绍了检查sql中的字符串格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我需要在sql中进行检查 - 找到提供的字符串格式为'YYYYMMdd'
请帮我这样做。
@datepart varchar(10)
@ datepart ='20130611'
i需要检查@datepart是否在表格中--YYYYMMdd。
谢谢事先,
解决方案
你可以使用正则表达式 [ ^ ]。
看看这里:
正则表达式使模式匹配和数据提取更容易 [ ^ ]
使用正则表达式搜索文本 [ ^ ]
使用正则表达式 [ ^ ]
另一种方法是用LIKE [ ^ ]和 PATINDEX [ ^ ]关键字:
搜索条件中的模式匹配 [ < a href =http://msdn.microsoft.com/en-us/library/ms187489%28v=sql.105%29.aspxtarget =_ blanktitle =New Window> ^ ]
或
使用 CONVERT [ ^ ]功能;)
示例:
DECLARE @ myStringDates TABLE (myDate VARCHAR ( 30 ))
INSERT INTO @ myStringDates (myDate)
SELECT ' 20130618' AS myDate
UNION ALL SELECT ' 18062013'
UNION ALL SELECT ' 06182013'
SELECT myDate
FROM @myStringDates
WHERE ( CONVERT ( INT , LEFT (myDate, 4 )) BETWEEN 1901 AND 2013 ) AND
( CONVERT ( INT ,SUBSTRING(myDate, 5 , 2 )) BETWEEN 1 AND 12 ) AND
( CONVERT ( INT ,SUBSTRING(myDate, 7 , 2 )) BETWEEN 1 AND 31 )
结果:
20130618
检查如下:
IF ISDATE(' 20130611')= 1
PRINT ' VALID'
ELSE
PRINT ' INVALID'
Hi all,
I need to do a check in sql - to find wheteher the supplied string is in the form 'YYYYMMdd'
please help me to do this.
@datepart varchar(10) @datepart = '20130611'
i need to check the @datepart is in the form - YYYYMMdd.
Thanks in advance,
解决方案
You can do it using Regex[^].
Have a look here:
Regular Expressions Make Pattern Matching And Data Extraction Easier[^]
Search Text with Regular Expressions[^]
Working with Regular Expressions[^]
Another way is to define pattern with LIKE[^] and PATINDEX[^] keyword:
Pattern Matching in Search Conditions[^]
Or
Using CONVERT[^] function ;)
Example:
DECLARE @myStringDates TABLE (myDate VARCHAR(30)) INSERT INTO @myStringDates (myDate) SELECT '20130618' AS myDate UNION ALL SELECT '18062013' UNION ALL SELECT '06182013' SELECT myDate FROM @myStringDates WHERE (CONVERT(INT, LEFT(myDate,4)) BETWEEN 1901 AND 2013) AND (CONVERT(INT, SUBSTRING(myDate,5,2)) BETWEEN 1 AND 12) AND (CONVERT(INT, SUBSTRING(myDate,7,2)) BETWEEN 1 AND 31)
Result:
20130618
Check bellow e.g
IF ISDATE('20130611') = 1 PRINT 'VALID' ELSE PRINT 'INVALID'
这篇关于检查sql中的字符串格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文