Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 convert data type Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 27, 2012 4:11 PM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 9:54 AM Points: 1,720, Visits: 3,063
 I would like to convert a column from varchar(10) to int.I also want to ignore any values that have charaters in it.I searched online and used this:select RecordId, [name],Cast(BuildingCode as int) as BuildingCodewhere BuildingCode Not LIKE '%[^0-9.-]%'but I don't actually know what does this Not LIKE '%[^0-9.-]%' mean?What ^ represent for?Thanks
Post #1389455
 Posted Wednesday, November 28, 2012 12:32 AM
 SSChampion Group: General Forum Members Last Login: Today @ 7:44 AM Points: 12,197, Visits: 9,148
 ^ is the negation. So %[^0-9.-]% means "every character that is not 0 through 9, a dot or the minus symbol".LIKE (Transact-SQL) How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at LessThanDot. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1389539
 Posted Wednesday, November 28, 2012 1:12 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, April 10, 2014 5:49 AM Points: 187, Visits: 699
 Here is a nice article which shows you why one would use this expression:http://www.sqlservercentral.com/articles/IsNumeric/71512/
Post #1389552
 Posted Wednesday, November 28, 2012 2:39 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, March 13, 2014 3:40 AM Points: 880, Visits: 668
 pls check below code:declare @t1 table(id int,name varchar(10))insert into @t1(id,name) values(1,'1'),(2,'2'),(3,'4'),(4,'7'),(5,'8_'),(9,'9'),(10,'0')select id,CONVERT(int,replace(name,'_','')) from @t1--CONVERT(int,replace(name,'_','')) from @t1where name like '%[^0_9]%'here name will come between 0 and 9 (zero,nine related records will not come)
Post #1389592
 Posted Wednesday, November 28, 2012 10:10 AM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 9:54 AM Points: 1,720, Visits: 3,063
 Koen Verbeeck (11/28/2012)^ is the negation. So %[^0-9.-]% means "every character that is not 0 through 9, a dot or the minus symbol".LIKE (Transact-SQL)Thanks, and thanks for site link, it makes more sense to me now.If this is the case, I think I don't need to include . and - sign.Because my building code always is a number, no negative, no decimals.So shall I just use not like %[^0-9]%
Post #1389954
 Posted Wednesday, November 28, 2012 10:25 AM
 SSChampion Group: General Forum Members Last Login: Today @ 7:44 AM Points: 12,197, Visits: 9,148
 sqlfriends (11/28/2012)Koen Verbeeck (11/28/2012)^ is the negation. So %[^0-9.-]% means "every character that is not 0 through 9, a dot or the minus symbol".LIKE (Transact-SQL)Thanks, and thanks for site link, it makes more sense to me now.If this is the case, I think I don't need to include . and - sign.Because my building code always is a number, no negative, no decimals.So shall I just use not like %[^0-9]%This is actually a double negation, so the following should work as well:LIKE '%[0-9]%'Be aware that using the % symbol at the start of a LIKE clause won't have good performance, as an index can't be used. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at LessThanDot. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1389972
 Posted Wednesday, November 28, 2012 10:34 AM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 9:54 AM Points: 1,720, Visits: 3,063
 Thanks, that makes sense.But I wonder why some people use : not like %[^0-9]%For my case, can I just change it to Like '[0-9]%'. What is the difference between like '%[0-9]%'and like '[0-9]%'.
Post #1389980
 Posted Wednesday, November 28, 2012 10:38 AM
 SSChampion Group: General Forum Members Last Login: Today @ 7:44 AM Points: 12,197, Visits: 9,148
 There's no logical difference between NOT LIKE '%[^0-9]%' and LIKE '%[0-9]%'. Because the first one is a double negation, it will be the same when you leave all the negations out. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at LessThanDot. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1389985
 Posted Wednesday, November 28, 2012 10:40 AM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 9:54 AM Points: 1,720, Visits: 3,063
 Thanks, how about the difference between like '%[0-9]%'and like '[0-9]%'.
Post #1389988
 Posted Wednesday, November 28, 2012 11:34 AM
 SSChampion Group: General Forum Members Last Login: Today @ 8:05 AM Points: 11,953, Visits: 10,984
 sqlfriends (11/28/2012)Thanks, how about the difference between like '%[0-9]%'and like '[0-9]%'. Are you familiar with with using wildcard searches? The first one will find any row that contains a 0-9 anywhere in the value. The second one will find any row that starts with 0-9. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1390017

 Permissions