selecting by criteria

[belcom125]

I need to create a criteria using LIKE or CASE or whatever that will identify only records with format ABC123. Where ABC is any 3 chars and 123 is any 3 numbers.

I tried lots of different ways with LIKE however can't come up with right one. Most time I don't have any results at all.

 

[dbomrrsm]

create table numalpha (field1 varchar (30))

insert into numalpha values ('ABC123')
insert into numalpha values ('AB3123')

--function isalpha
CREATE FUNCTION [dbo].[IsAlpha] 
(
	@Text varchar(255)
)
RETURNS BIT 
AS 


    BEGIN
    	DECLARE @Letters VARCHAR(255)
    	DECLARE @Index INT
    	DECLARE @Max INT
    	DECLARE @Pass BIT
    	SET @Letters = ' abcdefghijklmnopqrstuvwxyz'
    	SET @Max = LEN(@Text)
    	SET @Index = 0
    	SET @Pass = 1
    	WHILE @Index < @Max AND @Pass = 1
    	BEGIN
    		SET @Index = @Index + 1
    		IF NOT @Letters LIKE '%' + SUBSTRING(@Text, @Index, 1) + '%' SET @Pass = 0
    	END
    	RETURN(@Pass)
END

select * from numalpha
where dbo.isalpha(substring(field1,1,3)) = 1
and isnumeric(substring(field1,4,3)) = 1

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[donutman]

[Blue]DECLARE[/Blue] @Txt [Blue]varchar[/Blue][Gray]([/Gray]6[Gray])[/Gray]
[Blue]SET[/Blue] @Txt [Gray]=[/Gray] [red]'AB1123'[/red]
[Blue]SELECT[/Blue] @Txt [Blue]WHERE[/Blue] [Fuchsia]PatIndex[/Fuchsia][Gray]([/Gray][red]'%[^a-z]%'[/red][Gray],[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@Txt[Gray],[/Gray]1[Gray],[/Gray]3[Gray])[/Gray][Gray])[/Gray][Gray]+[/Gray]
                  [Fuchsia]PatIndex[/Fuchsia][Gray]([/Gray][red]'%[^0-9]%'[/red][Gray],[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@Txt[Gray],[/Gray]4[Gray],[/Gray]3[Gray])[/Gray][Gray])[/Gray][Gray]>[/Gray]0

DBomrrsm, you have to toss out that list of functions that you downloaded.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oops. I gave the set of not 'ABC123'. Change the > to =.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

I tink you might be right - there always seems an easier way !!

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

Even easier ;-)

WHERE mycol LIKE '[a-z][a-z][a-z][0-9][0-9][0-9]'

--James

 

[belcom125]

I actually realized it works like this:
WHERE MyCol LIKE '[a-z]'+'[a-z]'+'[a-z]'+'[0-9]'+'[0-9]'+'[0-9]'

the most simple solution

Thanks a lot to everyone who replied.