March 8, 2010 at 4:11 pm
I have a string, let's use the following example: 001_doug
What I want to be able to do is take a substring, and then run a check to see if the result is all numbers.
Right now I use the following:
SELECT STRING
FROM STUFF
WHERE SUBSTRING(STRING,1,3) like '[0-9][0-9][0-9]'
That would work great if all the substrings are of the same length. But they aren't, I'm actually passing in a value for each record that determines how long the substring should be.
So sometimes it will be like '[0-9]', others in might be like '[0-9][0-9][0-9][0-9]'.
I can do this in theory with dynamic sql and a bit of recursion but I was wondering if there was a superior way to do this.
March 8, 2010 at 4:18 pm
huston you can use something like this:
select
case
when myvalue like '%[^0-9]%'
then 'has chars'
else ;is numeric'
end
from mytable
you don't have to check each char in the string, just if any single non-numeric value exists or not, right?
now if you need to just strip the numeric portion or non-numeric portion off that might be a little different.
Lowell
March 8, 2010 at 11:31 pm
How about this?
DECLARE @SubStringLength AS INT
SELECT STRING
FROM STUFF
WHERE ISNUMERIC(SUBSTRING(STRING,1,@SubStringLength)) = 1 -- Checks the substring is numeric
AND SUBSTRING(STRING,1,@SubStringLength) NOT LIKE '%.%' -- Excludes rows with decimal point
March 9, 2010 at 9:13 am
I tried both of your suggestions.
The [^0-9] idea didn't work, I'm getting far more results than I should.
However, the isnumeric command worked perfectly, thank you.
March 10, 2010 at 10:39 am
you can use a tally or number table to split the string and do your comparison against each position.
-- create a temp tally table for test and fill it with sequential numbers starting at 1
SELECT TOP 100000 IDENTITY(INT,1,1) AS Number
INTO #Numbers
FROM master.INFORMATION_SCHEMA.COLUMNS i1
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2
CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i3;
GO
-- Add a primary key/clustered index to the numbers table
ALTER TABLE #Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number);
GO
--sample records
CREATE TABLE #SAMPLES ( Id int IDENTITY(1,1), TextVal varchar(512) NOT NULL )
INSERT INTO #SAMPLES VALUES ( '001_Doug')
INSERT INTO #SAMPLES VALUES ( '1234_SAM')
INSERT INTO #SAMPLES VALUES ( '1_JIM')
INSERT INTO #SAMPLES VALUES ( 'BOB')
INSERT INTO #SAMPLES VALUES ( '002_JIL_FRES')
--assuming the underscore is your delimiter. we want to look at everything left of the delimiter
SELECT
S.Id,
S.TextVal,
LEFT(S.TextVal, P.DelimiterPositionStop) as ConsideredText, --we only considered upto the delimeter
CASE WHEN P.HasChar = 0 THEN 'Non Number' ELSE 'Number' END as Result
FROM #SAMPLES S
LEFT JOIN ( --split out the first section using a tally table
SELECT
PV.ID,
MIN(CASE WHEN PV.SubstringText NOT LIKE '[0-9]' THEN 0 ELSE 1 END) as HasChar,
MAX(PV.Number) as DelimiterPositionStop
FROM ( --parsed values
SELECT
CV.Id, --the key for your table
N.Number, --the character position
SUBSTRING(CV.TextVal, N.number, 1) as SubstringText --value for each position upto the delimiter
FROM #SAMPLES CV
CROSS JOIN #Numbers N
WHERE N.Number < CHARINDEX('_', CV.TextVal + '_')
) PV
GROUP BY PV.Id) P ON P.Id = S.Id
March 10, 2010 at 11:27 am
"All numbers" is logically equivalent to a double negative of "NOT ( any non-numbers )", so just use
Column NOT LIKE '%[^0-9]%' -- the caret ( ^ ) means NOT and 0-9 is a range
SQL = Scarcely Qualifies as a Language
March 10, 2010 at 11:28 am
If a decimal point or commas are allowed, add the negation for these values
"All numbers" is logically equivalent to a double negative of "NOT ( any non-numbers )", so just use
Column NOT LIKE '%[^0-9^.^,]%' -- the caret ( ^ ) means NOT and 0-9 is a range
SQL = Scarcely Qualifies as a Language
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply