Return only values that are all numeric from a varchar
-
I have two tables, one with the original data, and a migrated one in need of fixing.
The migrated one has data for example in its object field:
411roadwork_08_eb.jpg
sampletest.jpg
15415286.JPG -- this one I need to match on
091913notext3.jpg
The record 15415286.JPG when I strip .jpg from it will correlate with an ID column of the original data. I'm trying to figure out how to strip the data without conversion errors so I can find how many actual match with the original data so I can fix them. I thought I knew how to do this but it's eluding me. :blink: I wish I could tell it that if it can't convert the trimmed record to an integer than to ignore it!
I'm looking at 460,000 records to weed actual candidates from.
-
Regular expressions should do it
DECLARE @t TABLE(col VARCHAR(50))
INSERT INTO @t(Col)
VALUES
('411roadwork_08_eb.jpg'),
('sampletest.jpg'),
('15415286.JPG'), -- this one I need to match on
('091913notext3.jpg');
SELECT Col
FROM @t
WHERE REPLACE(Col,'.jpg','') NOT LIKE '%[^0-9]%';
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Using SQL Server 2012, you could use TRY_CONVERT or TRY_CAST which will return a NULL value when a conversion can't be made.
Another option is described on the following article
http://www.sqlservercentral.com/Forums/Topic1028368-203-1.aspx
Here's a modified version for you.
SELECT *FROM (VALUES( '411roadwork_08_eb.jpg '),
('sampletest.jpg'),
('15415286.JPG'), -- this one I need to match on
('091913notext3.jpg '))x(String)
WHERE LEFT(String, CHARINDEX('.', String) - 1) NOT LIKE '%[^0-9]%'
-
Ah, Mark, that's what I was forgetting. Thanks!
-
Since you are just looking at characters before the first ".", then you could use something like:
SELECT
*
FROM
(
VALUES
( '411roadwork_08_eb.jpg '),
('sampletest.jpg'),
('15415286.JPG'), -- this one I need to match on
('091913notext3.jpg ')
)x(String)
WHERE
String NOT LIKE '%[^0-9]%.%'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply