convert data type

  • 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

  • ^ 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

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

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

  • 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)

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

  • 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

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

  • 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

  • Thanks,

    how about the difference

    between

    like '%[0-9]%'

    and like '[0-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/

  • 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/

  • Compare the following:

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

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

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

  • Thanks all.

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

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

  • 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

  • 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)

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

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