Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

convert data type Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 4:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 BuildingCode
where 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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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 @t1
where 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse