How to handle the validation getting left part num

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to handle the validation getting left part num

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-03-02 : 11:13:04

I need to get the left part number before dot, in some cases i don't have a filename with numbers at all to left, ignore those. Can you please tell me how i can exclude those filenames. in the below sameple data there are two which has no left part numbers and dot at all.

Thank you very much for the helpful info.

Declare @Sample table ([filename] varchar(100))
insert @Sample
select '168.765.Testfile1.pdf' union all
select '173.998.testfile2.pdf' union all
select 'Uid45612.html' union all
select 'myaudit123.pdf' union all
select '179.HlthStream.pdf'

select LEFT([filename], CHARINDEX('.', [filename]) - 1), [filename] as filename_s from @Sample ;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-02 : 11:44:25

use charindex to find the first dot. use substring to get the first substring up to the first dot. use


not like '%[^0-9]%'

to be sure all characters in the substring are numeric (literally "not any character not in the range 0 to 9"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources