none
请问16进制转成10进制为什么出错? RRS feed

答案

  • Hi Tim-2009,

     

    首先你的表达式错误的原因是 如果将 style 设置为 1,则 0x 必须作为前两个字符。 如果表达式中包含的字符数为奇数或者包含任何无效的字符,则会引发错误。你的字符数为11位是奇数。更多详情,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#binary-styles

     

    如果你想将16进制转换成10进制,我建议你使用下面的语句:

    SELECT 0x0011BD901 * 1 HextoInt或者  SELECT CONVERT(int, 0x0011BD901);

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    如果是把表中的varchar字段转成10进制,语句改如何写呢?
    • 已标记为答案 Tim-2009 2019年5月14日 7:50
    2019年5月13日 8:57
  • SELECT CONVERT(int,CONVERT(VARBINARY,stuff(case when v like '0x%' then '' else '0x' end+v, 3,0,left('0',len(v)%2)),1))
    from(values(N'0x0011BD901'),(N'0011BD901'),(N'0xa0011BD901'),(N'a0011BD901'))d(v) 这个可以就是把他的语句和我的结合了一下

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • 已标记为答案 Tim-2009 2019年5月14日 7:50
    2019年5月14日 6:10
  • WITH DATA AS(
    SELECT CONVERT(VARBINARY, 
    stuff(case when v like '0x%' then '' else '0x' end+v, 3,0,left('0',len(v)%2))
    ,1) as V
    from(values(N'0x0011BD901'),(N'0011BD901aa'),(N'0xa0011BD901aabb'),(N'a0011BD901aabbcc'))d(v)
    ),
    R AS(
    	SELECT v, v_10 = CONVERT(int, SUBSTRING(v,1,1)), position=1 FROM DATA
    	UNION ALL
    	SELECT v, v_10 = CONVERT(int, SUBSTRING(v,position+1,1)), position+1 FROM R WHERE position<DATALENGTH(v)
    )
    SELECT * FROM R ORDER BY v, position
    

    • 已标记为答案 Tim-2009 2019年5月14日 7:51
    2019年5月14日 6:17

全部回复

  • Hi Tim-2009,

     

    首先你的表达式错误的原因是 如果将 style 设置为 1,则 0x 必须作为前两个字符。 如果表达式中包含的字符数为奇数或者包含任何无效的字符,则会引发错误。你的字符数为11位是奇数。更多详情,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#binary-styles

     

    如果你想将16进制转换成10进制,我建议你使用下面的语句:

    SELECT 0x0011BD901 * 1 HextoInt或者  SELECT CONVERT(int, 0x0011BD901);

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年5月13日 8:25
  • Hi Tim-2009,

     

    首先你的表达式错误的原因是 如果将 style 设置为 1,则 0x 必须作为前两个字符。 如果表达式中包含的字符数为奇数或者包含任何无效的字符,则会引发错误。你的字符数为11位是奇数。更多详情,请参考:https://docs.microsoft.com/zh-cn/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#binary-styles

     

    如果你想将16进制转换成10进制,我建议你使用下面的语句:

    SELECT 0x0011BD901 * 1 HextoInt或者  SELECT CONVERT(int, 0x0011BD901);

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    如果是把表中的varchar字段转成10进制,语句改如何写呢?
    • 已标记为答案 Tim-2009 2019年5月14日 7:50
    2019年5月13日 8:57
  • SELECT CONVERT(VARBINARY,
    stuff(case when v like '0x%' then '' else '0x' end+v, 3,0,left('0',len(v)%2))
    ,1)
    from(values(N'0x0011BD901'),(N'0011BD901'),(N'0xa0011BD901'),(N'a0011BD901'))d(v)
    2019年5月14日 1:12
  • SELECT CONVERT(VARBINARY,
    stuff(case when v like '0x%' then '' else '0x' end+v, 3,0,left('0',len(v)%2))
    ,1)
    from(values(N'0x0011BD901'),(N'0011BD901'),(N'0xa0011BD901'),(N'a0011BD901'))d(v)

    请问这条语句中怎么查询结果还是16进制呢?
    2019年5月14日 2:07
  • SELECT CONVERT(int,CONVERT(VARBINARY,stuff(case when v like '0x%' then '' else '0x' end+v, 3,0,left('0',len(v)%2)),1))
    from(values(N'0x0011BD901'),(N'0011BD901'),(N'0xa0011BD901'),(N'a0011BD901'))d(v) 这个可以就是把他的语句和我的结合了一下

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • 已标记为答案 Tim-2009 2019年5月14日 7:50
    2019年5月14日 6:10
  • WITH DATA AS(
    SELECT CONVERT(VARBINARY, 
    stuff(case when v like '0x%' then '' else '0x' end+v, 3,0,left('0',len(v)%2))
    ,1) as V
    from(values(N'0x0011BD901'),(N'0011BD901aa'),(N'0xa0011BD901aabb'),(N'a0011BD901aabbcc'))d(v)
    ),
    R AS(
    	SELECT v, v_10 = CONVERT(int, SUBSTRING(v,1,1)), position=1 FROM DATA
    	UNION ALL
    	SELECT v, v_10 = CONVERT(int, SUBSTRING(v,position+1,1)), position+1 FROM R WHERE position<DATALENGTH(v)
    )
    SELECT * FROM R ORDER BY v, position
    

    • 已标记为答案 Tim-2009 2019年5月14日 7:51
    2019年5月14日 6:17