Finding an interval of numbers in a char column?
-
Hi there,
I have a column which have both characters and numbers.
Character A and B and numbers from 1 to 99.
How do I make a Query to find everything that's not A or B or a number from 1 to 99?
Thanks Joe joe
-
if the characters A & B are in the beginning or the end....
if in the beginning, you could do -
select colName from tbl where colName like 'A%' or colName like 'B%'
if in the end, you could do -
select colName from tbl where colName like '%A' or colName like '%B'
for the numbers - you would do the same thing except not like...
**ASCII stupid question, get a stupid ANSI !!!** -
Hi sushila,
Thanks for your advice. There are all sorts of combinations, A1, A, 1, B99 ect. Finding what's not A or B is easy, but I would like to have an easy way to find out if there is numbers over 99 (without having to write 'not like' 99 times).
Regards joejoe
-
Got a suggestion, but it depends on what the data looks like.
This won't work is you have values like A10B54 or 10B10 but is fine if the entries are like B23, A34, AB56, A43B, 51, AB, etc
If this won't work, could you post some sample data, so we can see what we're working with.
That said, try this...
Select colname FROM tbl where
CAST(REPLACE(REPLACE(colname,'A',''),'B','') AS INT)>99
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
joejoe..another method - again, as gilamonster says - won't work if the characters are anywhere embedded in your string...
select colname from tbl where
cast(substring(colname, 2, len(colname) - 1) as integer) > 99
**ASCII stupid question, get a stupid ANSI !!!** -
Joejoe - if you need values that are embedded like a24b101, then you can use this code - else - just follow the previous suggestions.
First of all - the way this is written, it requires a function that I found somewhere - and modified to handle any delimiter of any length. Have to aplogize for the original credits are gone - but this is what happens when you let an intern help you out. Gotta love interns.. Or is that You goota shoot interns???
--Code for the function
CREATE FUNCTION dbo.fn_Split(@String nvarchar(4000), @Delimiter nvarchar(5))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice nvarchar(4000)
DECLARE @TMPINDEX INT
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @index = 1
-- following line added 10/06/03 as null
-- values cause issues
IF @String IS NULL RETURN
WHILE @index !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @index = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @index !=0
SELECT @slice = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @slice = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @TMPINDEX = LEN(@STRING) - (@INDEX + len(@Delimiter))
IF @TMPINDEX < 0 BREAK
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - (@INDEX + (len(@Delimiter)-1)))
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
--End Function Code
Now the actual code...
DECLARE @Concept1 TABLE (Value VARCHAR(50))
DECLARE @Working TABLE (SplitVal VARCHAR(25), tmpValue VARCHAR(50))
DECLARE @Split1 VARCHAR(25), @Split2 VARCHAR(25), @tmpVar VARCHAR(50), @Limiter INT
SET @Split1 = 'A' --Possible embedded value 1
SET @Split2 = 'B' --Possible embedded value 2
SET @Limiter = 99 --Value to be greater than
--Add the Simple Values
INSERT INTO @Concept1 VALUES('B24')
INSERT INTO @Concept1 VALUES('A21')
INSERT INTO @Concept1 VALUES('B11')
INSERT INTO @Concept1 VALUES('A8')
INSERT INTO @Concept1 VALUES('B101')
INSERT INTO @Concept1 VALUES('B6')
INSERT INTO @Concept1 VALUES('A59')
INSERT INTO @Concept1 VALUES('A88')
INSERT INTO @Concept1 VALUES('B77')
INSERT INTO @Concept1 VALUES('A41')
INSERT INTO @Concept1 VALUES('B92')
INSERT INTO @Concept1 VALUES('A115')
--Add The Complex Values
INSERT INTO @Concept1 VALUES('B81B92A11')
INSERT INTO @Concept1 VALUES('A22B115A191')
INSERT INTO @Concept1 VALUES('B1A100A2')
INSERT INTO @Concept1 VALUES('A80A81A82')
--The Whole Sectrion above should be replaced by a select distinct from your source table
--Update the Concept table now....
UPDATE @Concept1 SET VALUE = REPLACE(REPLACE(VALUE, @Split1, ',' + @Split1), @Split2, ',' + @Split2)
--Loop Through the Begin table
WHILE EXISTS(SELECT TOP 1 1 FROM @Concept1)
BEGIN
SELECT @tmpVar = Value from @Concept1
INSERT INTO @Working
SELECT
'', Items
FROM
dbo.fn_Split(@tmpVar, ',')
WHERE Items <> ''
DELETE FROM @Concept1 WHERE Value = @tmpVar
END
UPDATE @Working
SET
SplitVal = @Split1,
tmpValue = Replace(tmpValue, @Split1, '')
WHERE tmpValue LIKE @Split1 + '%'
UPDATE @Working
SET
SplitVal = @Split2,
tmpValue = Replace(tmpValue, @Split2, '')
WHERE tmpValue LIKE @Split2 + '%'
SELECT * FROM @Working
WHERE CONVERT(INT, tmpValue) > @Limiter
--End Code
A few notes. The variables @Split1 and @Split2 are curtrently set to A and B respectively. You can set them to any value you want to exclude. If there are more than the two letters you want to exclude - you will have to tweak the code to reflect this. Where I do an insert into @Concept1 Values... you will want to pull this sectiion out and replace with a "Insert into @Concept1 SELECT Distinct YourField FROM YourTable". Other than that - the code is ready to go - tested it to make sure my concept worked...
Hope it helps - and let me know if you have any questions.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
Thanks to all of you
I'm lucky that the combination "only" is A34 or B01 or just 01, never A56B67 ect. but thanks alot Learn2Live!
I have tried to tweek GilaMonster's
CAST(REPLACE(REPLACE(colname,'A',''),'B','') AS INT)>99
a littel to give me all the records with something else than numbers from 1 to 99 or A or B. But I think that I'm messing up with the OR syntax.
Regards Joejoe
-
joe joe - if i understand you correctly, you want all the rows that neither begin with A or B nor have numbers less than 99...if so, the query I gave you should work....did you try it out ?! did you not get the right resultset with that ?!
select colname from tbl where
cast(substring(colname, 2, len(colname) - 1) as integer) > 99
**ASCII stupid question, get a stupid ANSI !!!** -
Hi Sushila,
I must have misunderstood the code, I thought that it cut away the first character, without knowing if it is A, B or G???
I have just tried it, it gives me the following message,
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
Regards joejoe
-
you're right joe joe - my mistake - try this instead...Hopefully I've understood the requirement correctly this time:
select * from myTable
where colName not like 'a%' and colName not like 'b%'
and cast(colName as int) > 99
**ASCII stupid question, get a stupid ANSI !!!** -
I hate to point out the obvious, but CAST('A102' AS INT) gives 'Syntax error converting the varchar value 'A102' to a column of data type int.'
SELECT * FROM tbl WHERE
colname NOT IN ('A','B') AND
CAST(REPLACE(REPLACE(colname,'A',''),'B','') AS INT)>99
I created a table to test it and it seems fine
CREATE TABLE tbl (colname VARCHAR(10))
GO
INSERT INTO tbl VALUES ('A')
INSERT INTO tbl VALUES ('A34')
INSERT INTO tbl VALUES ('B01')
INSERT INTO tbl VALUES ('B102')
INSERT INTO tbl VALUES ('159')
INSERT INTO tbl VALUES ('89')
GO
Resultset:
colname
----------
B102
159
I think, from what I understand, that that is what you want, but please shout if it isn't exact and I'll be happy to fix it
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks GilaMonster this was precisly what I was looking for
I've tested it and its perfect.
But now I found out that ex. B1 or B88 also is an error.
Is there a way to check for if the column holds A and B combinated with numbers? (and still get the ones with other characters than A or B or numbers over 99)
Once again thanks to all you helpfull people out there
Joejoe
-
To Gilamonster...I wasn't casting 'A102' - only those colNames where the strings did not begin with 'A' or 'B' - that's because it seems I misunderstood the requirements...yet again...
I was getting all the colNames that had no alphas in them - only numeric > 99...
create table myTable
(colName varchar(6))
insert into myTable values('a12345')
insert into myTable values('a00045')
insert into myTable values('b12345')
insert into myTable values('a00005')
insert into myTable values('512345')
insert into myTable values('000006')
insert into myTable values('b00020')
insert into myTable values('000100')
insert into myTable values('b56789')
insert into myTable values('200')
insert into myTable values('20')
insert into myTable values('89')
insert into myTable values('44')
insert into myTable values('900')
insert into myTable values('b1')
insert into myTable values('b88')
select * from myTable
where colName not like 'a%' and colName not like 'b%'
and cast(colName as int) > 99
**ASCII stupid question, get a stupid ANSI !!!** -
Oops, my bad. Sorry.
No disrespect or insult was intended.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
none was taken gilamonster...just felt i had to defend myself & mine is all...
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply