Pattern Analysis

[charliescott]

Hello,

Just wondering if there was a cleaner way to perform a pattern analysis then using the following code:

USE Northwind

SELECT CustomerID,PostalCode,Replace(Replace(Replace(Replace(Replace(Replace(Replace
(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
(Replace(Replace(Replace(PostalCode,'A','C'),
'B','C'),'D','C'),'E','C'),'F','C'),'G','C'),'H','C'),'I','C'),'J','C'),'K','C'),
'L','C'),'M','C'),'N','C'),'O','C'),'P','C'),'Q','C'),'R','C'),'S','C'),'T','C'),
'U','C'),'V','C'),'W','C'),'X','C'),'Y','C'),'Z','C'),'0','N'),'1','N'),'2','N'),
'3','N'),'4','N'),'5','N'),'6','N'),'7','N'),'8','N'),'9','N'),'a','c'),'b','c'),
'd','c'),'e','c'),'f','c'),'g','c'),'h','c'),'i','c'),'j','c'),'k','c'),'l','c'),
'm','c'),'n','c'),'o','c'),'p','c'),'q','c'),'r','c'),'s','c'),'t','c'),'u','c'),
'v','c'),'w','c'),'x','c'),'y','c'),'z','c') AS 'Pattern'
INTO #Temp01
FROM Customers

SELECT Pattern
FROM #Temp01
GROUP BY Pattern

 

[vongrunt]

In other words: alpha characters are either C or c, while numeric chars are N in result patterns?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[charliescott]

Vongrunt, that is correct.

 

[gmmastros]

Is it important to preserve case sensitivity?

A -> C
a -> c (lowercase c)

This probably isn't much better than what you are currently doing, but it is probably a little easire to read.

Declare @Zip VarChar(10)
Set @Zip = 'abc123'

Select Case When IsNumeric(SubString(@Zip, 1, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 2, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 3, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 4, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 5, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 6, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 7, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 8, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 9, 1)) = 1 Then 'N' Else 'C' End +
       Case When IsNumeric(SubString(@Zip, 10, 1)) = 1 Then 'N' Else 'C' End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

This will also take care of -,+ and other inwanted characters a U will be displayed

use pubs
go 
create table #Customers (PostalCode varchar(50))
insert into #Customers values ('12345')
insert into #Customers values ('12A45')
insert into #Customers values ('123B5')
insert into #Customers values ('1--45')
insert into #Customers values ('123Za')



select PostalCode,case when  ascii(substring(PostalCode,1,1))  between 48 and 57 then 'N' 
when  ascii(substring(PostalCode,1,1))  between 65 and 90 then 'C'
when  ascii(substring(PostalCode,1,1))  between 97 and 122 then 'c'
else 'U' end +
case when  ascii(substring(PostalCode,2,1))  between 48 and 57 then 'N' 
when  ascii(substring(PostalCode,2,1))  between 65 and 90 then 'C'
when  ascii(substring(PostalCode,2,1))  between 97 and 122 then 'c'
else 'U' end+
case when  ascii(substring(PostalCode,3,1))  between 48 and 57 then 'N' 
when  ascii(substring(PostalCode,3,1))  between 65 and 90 then 'C'
when  ascii(substring(PostalCode,3,1))  between 97 and 122 then 'c'
else 'U' end+
case when  ascii(substring(PostalCode,4,1))  between 48 and 57 then 'N' 
when  ascii(substring(PostalCode,4,1))  between 65 and 90 then 'C'
when  ascii(substring(PostalCode,4,1))  between 97 and 122 then 'c'
else 'U' end+
case when  ascii(substring(PostalCode,5,1))  between 48 and 57 then 'N' 
when  ascii(substring(PostalCode,5,1))  between 65 and 90 then 'C'
when  ascii(substring(PostalCode,5,1))  between 97 and 122 then 'c'
else 'U' end
from #Customers




drop table #Customers

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[charliescott]

gmmastros - thanks for this suggestion, however, i am interested in maintaining case sensitivity in addition to nonalphanumeric characters.

code works as is, but i was just wondering if there was another function within sql that i could have utilzed instead of replace.

thanks again for your help!

 

[charliescott]

Denis - I like this solution too!!! Any suggestions as to making this dynamic and not tied to the specific length of each field?

 

[SQLDenis]

You could create a UDF that will have this code and then call that UDF from the select

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

That said, original buncha replaces don't do it well...

... because numbers are translated to 'N' and then 'N' is translated to 'C' or 'c' depending on case-sensitivity.



------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[vongrunt]

... actually only to 'c', assuming case-insensitive DB/collation.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[SajidAttar]

create function dbo.GetStringPatern ( @String varchar(500))
returns varchar(500) as

begin
declare @NString varchar(500), @LenString int, @Char char(1)
set @LenString = len(isnull(@string,''))

while @lenstring > 0
begin
select @Char = left (@String,1)

select @NString = isnull(@NString, '') + 
		case when ascii(@Char) between 65 and 90 then 'C'
		     when ascii(@Char) between 97 and 122 then 'c'
		     when ascii(@Char) between 48 and 57 then 'N'
		     when isnumeric(@Char) = 1 then 'n'
		     else 'U' END 
set @LenString = @LenString - 1
Set @String = Right (@String, @LenString)

end

return @Nstring

end

go
-- Example, (C = Caps char; c
select dbo.GetStringPatern ('SajidAttar@Tek-Tips*SQL2000')

I hope this will help you.

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell