Find Position of 1st Number

• Triality

  SSCommitted

  Points: 1958

  More actions

  February 9, 2012 at 10:28 pm

  #391526

  Hello -

  I am trying to use SUBSTRING / CHARINDEX to select from the first position of a varchar column to the first number. Here is what I have so far, but it is not working:

  SELECT

  SUBSTRING(Product_Name, 1, CHARINDEX ('%[0-9]%', Product_Name))

  FROM dbo.Products

  The column data will look like this:

  1) Product 123

  2) Another Product 14 XX

  3) 12

  I would want to select:

  1) Product

  2) Another Product

  3) null

  Thanks!

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 9, 2012 at 10:58 pm

  #1445375

  Try PATINDEX

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 9, 2012 at 11:03 pm

  #1445377

  Like this:

  DECLARE @Table TABLE ( TextData VARCHAR(100))

  INSERT INTO @Table ( TextData )

  SELECT 'Product 123'

  UNION ALL SELECT 'Another Product 14 XX'

  UNION ALL SELECT '12'

  SELECT NewText =

  CASE WHEN LEN(CrsApp.NewText) > 1

  THEN CrsApp.NewText

  ELSE NULL

  END

  FROM @Table

  CROSS APPLY (SELECT SUBSTRING(TextData,0,PATINDEX('%[0-9]%',TextData)) ) CrsApp(NewText)