Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Select Numbers which start with a number and zeros after

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%') 

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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]';
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading