July 31, 2003 at 9:22 am
create table t1 (ID int, comments varchar(1000))
insert into t1
select 1, '1234_I wish someone who knew'
UNION
select 2, '55232_how to design an application'
UNION
select 3, 'was involved in this _process_'
UNION
select 4, '131244_before I ended up in the mess.'
UNION
select 5, '131with text inside 244_Here's the case the screws up my less than.'
UNION
select 6, '131!244_Other characters that cause trouble.'
UNION
select 7, '131 244_I feel like it has to be parsed unless there is a reg exp.'
From the seed data given, you can see that three of the rows follow the rule that a comment is preceded by a number and an underscore.
I can parse out the integer using a select statement like this:
select substring(comments,0,charIndex('_', comments)) as subString,
charIndex('_', comments) as positionOfUnderscore,
comments, ID
from t1
where comments < 'a'
Is there a better way to do this?
I'm looking for an isInt function or a regular expression that can be applied.
EDIT OF ORIGINAL POST:
The select statement that I wrote doesn't handle the additional cases I added above.
Edited by - jraha on 07/31/2003 09:37:04 AM
July 31, 2003 at 9:31 am
If this works, I can't see why you would want to do something else. In fact, I'm going to hold on to this script, just in case.
July 31, 2003 at 12:39 pm
Here's a set based way that I think will work for all cases.
(Note that an integer can only be up to 10 digits.)
drop table tIntegerTester
create table tIntegerTester (id int, col1 char(1), col2 char(1), col3 char(1), col4 char(1), col5 char(1),
col6 char(1), col7 char(1), col8 char(1), col9 char(1), col10 char(1))
insert into tIntegerTester
select ID,
case when charIndex('_', buysectionName)>1 then substring(buysectionName,1,1) else null end,
case when charIndex('_', buysectionName)>2 then substring(buysectionName,2,1) else null end,
case when charIndex('_', buysectionName)>3 then substring(buysectionName,3,1) else null end,
case when charIndex('_', buysectionName)>4 then substring(buysectionName,4,1) else null end,
case when charIndex('_', buysectionName)>5 then substring(buysectionName,5,1) else null end,
case when charIndex('_', buysectionName)>6 then substring(buysectionName,6,1) else null end,
case when charIndex('_', buysectionName)>7 then substring(buysectionName,7,1) else null end,
case when charIndex('_', buysectionName)>8 then substring(buysectionName,8,1) else null end,
case when charIndex('_', buysectionName)>9 then substring(buysectionName,9,1) else null end,
case when charIndex('_', buysectionName)>10 then substring(buysectionName,10,1) else null end
from t1
where charIndex('_', buysectionName) > 0 and
charIndex('_', buysectionName) < 11
select convert(int,substring(buysectionName,0,charIndex('_', buysectionName))) from tIntegerTester IT
join t1 P on P.id = IT.id
where col1 >= '0' and col1 <= '9' and
((col2 >= '0' and col2 <= '9') or col2 is null) and
((col3 >= '0' and col3 <= '9') or col3 is null) and
((col4 >= '0' and col4 <= '9') or col4 is null) and
((col5 >= '0' and col5 <= '9') or col5 is null) and
((col6 >= '0' and col6 <= '9') or col6 is null) and
((col7 >= '0' and col7 <= '9') or col7 is null) and
((col8 >= '0' and col8 <= '9') or col8 is null) and
((col9 >= '0' and col9 <= '9') or col9 is null) and
((col10 >= '0' and col10 <= '9') or col10 is null)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply