convert data type

Question

Post reply

convert data type

sqlfriend

SSC Guru

Points: 52781
More actions
November 27, 2012 at 4:11 pm

#270701

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

^ is the negation. So %[^0-9.-]% means "every character that is not 0 through 9, a dot or the minus symbol".

LIKE (Transact-SQL)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

WolfgangE SSCertifiable Points: 6328 More actions · Answer 2

Here is a nice article which shows you why one would use this expression:

http://www.sqlservercentral.com/articles/IsNumeric/71512/

subbu1 Hall of Fame Points: 3281 More actions · Answer 3

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)

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

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]%

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

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]%'.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

Thanks,

how about the difference

between

like '%[0-9]%'

and like '[0-9]%'.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Koen Verbeeck (11/28/2012)
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.

Actually this won't work. The second one here will find anything that contains a number. The first one will return only those contain nothing but a number. See the code below for an example. Play around with the where clauses and you will see what I mean.

;with cte (SomeVal) as

(

select '9394' union all

select 'a939' union all

select '9865a847' union all

select 'no number'

)

select * from cte

--where SomeVal like '%[0-9]%' --This one returns three of the rows because they contain a number somewhere in the value

--where SomeVal not like '%[^0-9]%' --this one returns only rows that have nothing but numbers

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Compare the following:

DECLARE @test1 VARCHAR(10) = '01ADA10';

SELECT @test1 WHERE @test1 LIKE '%[0-9]%';

SELECT @test1 WHERE @test1 NOT LIKE '%[^0-9]%';

sqlfriend SSC Guru Points: 52781 More actions · Answer 12

Thanks all.

Then for my case, my buildingcode is always a number, I will use:

NOT LIKE '%[^0-9]%';

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

Sean Lange (11/28/2012)
Actually this won't work. The second one here will find anything that contains a number. The first one will return only those contain nothing but a number. See the code below for an example. Play around with the where clauses and you will see what I mean.
;with cte (SomeVal) as
(
select '9394' union all
select 'a939' union all
select '9865a847' union all
select 'no number'
)
select * from cte
--where SomeVal like '%[0-9]%' --This one returns three of the rows because they contain a number somewhere in the value
--where SomeVal not like '%[^0-9]%' --this one returns only rows that have nothing but numbers

Whoops, I stand corrected. That will teach me to make bold statements without testing them first.

Thanks (and Lynn, you as well) for the code example.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

Koen Verbeeck (11/28/2012)
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.

Absolutely NOT true. Try both on "JBM1" and see. The double negation is required to successfully exclude alpha characters.

{Edit} Dang it. I thought I'd scrolled down all the way. I see that Lynn already picked up on the correction.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)