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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy