-1

In fact there are 2 questions, 2 queries:

  • I'd need to find out is a numeric that beginning with '541' is 18 chars long In fact there is a space right after SO: Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when....'541'....15RandomNumbers in (0,1,2,3,4,5,6,7,8,9).....an unknown printer took a galley of type and scrambled it to make a type specimen book.
  • Second: Check if last 18 chars of a string are numeric (maybe after a trim right)

That's it,

laurens
  • 497
  • 14
  • 28
  • 1
    Try `SUBSTRING` and `ISNUMERIC` – Morphed Apr 17 '12 at 09:26
  • @Paddy Post this as answer and I will happily upvote, just because there's nothing more to say. – fancyPants Apr 17 '12 at 09:32
  • @tombom: maybe for you there is nothing more to say...? Very sorry but I don't see code yet that defines that the last 18chars of the field are numeric ? isnumeric is not something I don't know – laurens Apr 17 '12 at 09:36
  • @Arion: I changed the question a little: it's just a random text: a form on a website in fact. But, you can forget the first part of my question, the second is more important. Thx – laurens Apr 17 '12 at 09:40
  • 1
    @laurens It's the combination of `SUBSTRING` and `ISNUMERIC`! "but I don't see code yet", pfff. This is not a "do my work for me" site. Use your own brain if you want to become good at something! – fancyPants Apr 17 '12 at 09:42
  • @laurens you might also use `RIGHT()` – fancyPants Apr 17 '12 at 09:42

2 Answers2

2

"find out is a numeric that beginning with '541' is 18 chars long"

( text_col NOT LIKE '541%' OR LEN(text_col) = 18 )

"Check if last 18 chars of a string are numeric (maybe after a trim right)"

   ( RTRIM(text_col) LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
1

Some data to start you off

declare @tmp table (value nvarchar(30))
insert @tmp values ('ABC123456789123456789'),('ABC12345678912345678A'),('ABC123456789123456D78')

Look at RIGHT and ISNUMERIC

SELECT t.value, ISNUMERIC(RIGHT(t.value,18)+'.0e0') as [IsNumeric]
FROM @tmp t

Gives:

value                          IsNumeric
------------------------------ -----------
ABC123456789123456789          1
ABC12345678912345678A          0
ABC123456789123456D78          0

Edit: I've amended my answer as per the feedback from Damien_The_Unbeliever and it has fixed the problem he raises

Morphed
  • 3,527
  • 2
  • 29
  • 55
  • Sorry, I meant, I would upvote you for not posting actual code – fancyPants Apr 17 '12 at 09:50
  • Thank you! In fact, not so hard... but everyone his specialties right – laurens Apr 17 '12 at 09:54
  • This is the answer to your second question, you'll need to do something similar for your first - look at Google for SQL `LEFT` – Morphed Apr 17 '12 at 09:58
  • `ISNUMERIC` is rather flaky. For instance, change your second value to `'ABC123456789123456D78'`, and it will return a 1. – Damien_The_Unbeliever Apr 17 '12 at 09:58
  • @laurens http://mattgemmell.com/2008/12/08/what-have-you-tried/ <-- this is my business. Belgians........ – fancyPants Apr 17 '12 at 10:00
  • @Damien_The_Unbeliever Hmm, you're right, might have to start my own SO question about that! – Morphed Apr 17 '12 at 10:20
  • @Paddy - there are plenty of questions already on here that discuss it in either the question or the answers. Long story short - it answers a question nobody ever actually wants to ask ("Can this string be converted to any of the numeric types?" rather than the more sensible "Can this string be converted to an int?" (or replace "int" with any specific numeric type)) – Damien_The_Unbeliever Apr 17 '12 at 10:22
  • @Damien_The_Unbeliever -Very true, I've edited my answer with the suggestion from http://stackoverflow.com/a/5988983/1016183. – Morphed Apr 17 '12 at 10:33
  • @laurens Don't know if you missed the edit but the addition of `.0e0` (as per my previous comment). Fixes the issue raised by `Damien_The_Unbeliver`. This answer may perform better than the other due to the `LIKE` clause. – Morphed Apr 19 '12 at 10:00