SQL Server Select Numbers which start with a number and zeros after

bkhjykvo 于 2023-05-05 发布在其他

关注(0)|答案(2)|浏览(219)

enter image description here I am trying to select only rounded numbers like 1,1000,100000,200000,4000 from a large data set I want to select all rows with amount starting with whole numbers 1,2,3,4,5,6,7,8,9 and zeros after or just the whole number itself without zeros after.

SELECT [INPUTTER]
      ,[OUR_REFERENCE]
      ,[TRANS_REFERENCE]
      ,[COMMON_REF]
      ,[RECID]
      ,[ACCOUNT_NUMBER]
      ,[TRANSACTION_CODE]
      ,[NARRATIVE]
      ,[AMOUNT_LCY]
      ,[AMOUNT_FCY]
      ,[VALUE_DATE]
      ,[BOOKING_DATE]
      ,[CURRENCY]
      ,[EXCHANGE_RATE]
      ,[PRODUCT_CATEGORY]
      ,[PL_CATEGORY]
      ,[REVERSAL_MARKER]
  FROM [Steward].[dbo].[vwSTMT_01]

  v
  where AMOUNT_LCY LIKE '[1-9]%' and AMOUNT_LCY NOT LIKE '[1-9]%[^0]';

Sample data set

https://drive.google.com/file/d/1KEhEVI_y9ANDQdMF2oGaduyZmHr4zNcL/view?usp=share_link

sql-server

来源：https://stackoverflow.com/questions/76073124/select-numbers-which-start-with-a-number-and-zeros-after

2条答案

按热度按时间

csbfibhn1#

Seems like you could use a couple of LIKE expressions here. First you can use LIKE '[1-9]%' to get only get rows that start with a numerical character (I assume they can't start with a 0 but you can add that), and then NOT LIKE '[1-9]%[^0]' to exclude any rows that have a character that isn't a 0 after the first digit:

SELECT *
FROM (VALUES('1'),
            ('20'),
            ('31'),
            ('400'),
            ('56238'),
            ('600000'),
            ('a00'))V(ID)
WHERE V.ID LIKE '[1-9]%'
   AND V.ID NOT LIKE '[1-9]%[^0]%';

赞(0）回复(0）举报 2023-05-05

mznpcxlj2#

You could parse it to a decimal and check if it has a fractional part using modulo.

SELECT *
FROM YourTable t
CROSS APPLY (VALUES
    TRY_CAST(TRIM('()' FROM t.YourColumn) AS decimal(18,9))
)) v(Trimmed)
WHERE v.Trimmed % 1.0 > 0
  AND CAST(v.Trimmed / POWER(10E0, FLOOR(LOG10(v.Trimmed))) AS decimal(18, 9)) % 1.0;

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server Select Numbers which start with a number and zeros after

2条答案

相关问题

热门标签

最新问答