1

I have a column (PRODUCTNUMBER) of type VARCHAR that can contain data like this:

SEC123456
SEC-123456
12-35-46
123456

Is it's possible to run a SQL Server query where I only send '123456' as my search data and will get a match for all of the rows that PRODUCTNUMBER has this data in it (even if PRODUCTNUMBER data contain characters like 'SEC123456')?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Raskolnikoov
  • 565
  • 11
  • 27

2 Answers2

1

First Create Function to Extract Numeric Value

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

then 

select * from TableName where dbo.udf_GetNumeric(PRODUCTNUMBER) like '%123456%'

try this

Sudhir Panda
  • 774
  • 1
  • 7
  • 27
  • This works, but Is very slow when U have millions of records. But it's my best solution so for. I Think I have to rethink and add another column in my database with a numeric value from the other column that can contain other than numeric characters. – Raskolnikoov Dec 16 '15 at 07:31
0

For the case of contiguous characters:

select * from t where productnumber like '%123456%'

would work. If it's just '-' in the data you can use replace(productnumber, '-', ''). But if it could be many other non-numeric characters, you want something like:

SQL to return field with non-numeric characters removed in MySQL

Ian McGowan
  • 3,461
  • 3
  • 18
  • 23
  • The issue with the LIKE (%value%) operator is that if I have data that is "1234567" och "0123456" they will also be a match if I query "123456". – Raskolnikoov Dec 16 '15 at 07:23