0

I am trying to use the PATINDEX function in SQL Server 2008 R2 to extract the value 3 from the string 

Charged Hourly Fee for 3 CR for BCP202DL Personal Development II

but I seem to be making a mistake.

I tried 

SELECT PatIndex('%[0-9]%', 'Charged Hourly Fee for 3 CR for BCP202DL Personal Development II')

which returns the position 24 yet I want the value 3.

Could someone assist with the solution?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
James Obuhuma
  • 397
  • 3
  • 8
  • 20
  • Could you please post your own take on the problem? It'd be easier to point out your mistake. – AdamL Mar 20 '13 at 12:20
  • Please add the expected output. – TechDo Mar 20 '13 at 12:29
  • I tried "SELECT PatIndex('%[0-9]%', 'Charged Hourly Fee for 3 CR for BCP202DL Personal Development II')" which returns 24 yet I want the value 3? – James Obuhuma Mar 20 '13 at 12:31
  • 2
    `24` is the **position** of the number `3`. You need to take a look at `SUBSTRING`, and pass the position found with `PATINDEX`. You're almost there! – Danny Beckett Mar 20 '13 at 12:54

3 Answers3

2

Please try:

Select substring(Data, PatIndex('%[0-9]%', Data), 1)
from(
    select 'Charged Hourly Fee for 3 CR for BCP202DL Personal Development II' as Data
)x
TechDo
  • 18,398
  • 3
  • 51
  • 64
0

If you must use the patindex function and the description of your item will never contain another number such as "3" you can use the following:

select patindex('%3%', 'Charged Hourly Fee for 3 CR for BCP202DL Personal Development II')

This will return 24.

I suspect that you are looking at a string where the quantity ordered is always in the same position. In that case, I would use the substring function.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Michael Harmon
  • 746
  • 5
  • 10
  • 1
    Thanks. However this returns the position 24 as you mentioned yet I want to extract the value "3". The value is not always "3" but it always appears in that position. How can I achieve this? Maybe I need not to use patindex? – James Obuhuma Mar 20 '13 at 12:30
0
declare @str nvarchar (max)
set @str='Charged Hourly Fee for 3 CR for BCP202DL Personal Development II'
Select substring(@str, PatIndex('%[0-9]%', @str), 1)

this will return first digit in the string.

The way you were doing was returning the position of the first digit, and my code will return value at that position.

Ankit
  • 680
  • 4
  • 17