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.
I have tried using sql queries but i am failing to get what I need specifically. Please assist.
SELECT TOP (1000) [RECID]
,[ACCOUNT_NUMBER]
,[TRANS_REFERENCE]
,[INPUTTER]
,[ACCOUNT_OFFICER]
,[PL_CATEGORY]
,[PRODUCT_CATEGORY]
,[AMOUNT_LCY]
,[AMOUNT_FCY]
,[CURRENCY]
,[BOOKING_DATE]
,[DATE_TIME]
,[VALUE_DATE]
,[COMPANY_CODE]
,[TRANSACTION_CODE]
,[CUSTOMER_NO]
,[OUR_REFERENCE]
,[NARRATIVE]
FROM [Steward].[dbo].[CATEG_01]
z
where
z.AMOUNT_LCY LIKE ('10000%')
>Solution :
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]';