extracting part of string for distinct values 1

[lorca]

Hi

I have a SQL table witch column LICTYPE which holds such values as

06/xxxxx/LIPREM
06/yyyyy/LIPREM
06/xxxyy/CLUB
06/yyxxx/CLUB
05/xxxxy/LATEMP

In an ASP form, i'd like to have a select list of distinct LICTYPES such as

LIPREM
CLUB
LATEMP
etc etc

so i need to loop through the contents of column LICTYPE to extract the text using something like ?

MyVar = Col003
MyArray = Split(MyVar,"/")
MyTextSel = Ubound(MyArray)
MyLicTypes = MyArray(MyTextSel)

I want to then hold the distinct values to display in a drop-down select on an ASP form ?

I thought a stored procedure might be ideal, but how could i best write this ?


thanks
kim

 

[philhege]

SELECT DISTINCT
REVERSE(
SUBSTRING(reverse(LICTYPE),1,charindex('/',reverse(LICTYPE))-1)
)
as lictype_extracted
FROM Mytable
ORDER BY lictype_extracted

The only caveat is that every field must have the info/info/lictype construction, or at least one forward slash.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[lorca]

Thanks Phil

almost perfect................this works ok

SELECT DISTINCT
REVERSE(SUBSTRING(reverse(LICTYPE),1,charindex('/',reverse(LICTYPE))))
as lictype_extracted
FROM Mytable
ORDER BY lictype_extracted

but shows
/LIPREM
/CLUB

I don't need the slash

with the '-1' back in it errors as
'invalid length parameter passed to the substring function'

SELECT DISTINCT
REVERSE(SUBSTRING(reverse(LICTYPE),1,charindex('/',reverse(LICTYPE))-1))
as lictype_extracted
FROM Mytable
ORDER BY lictype_extracted

thanks

 

[SQLDenis]

this works for me

create table #test (SomeField varchar(66))
insert into #test values ('06/xxxxx/LIPREM')
insert into #test values ('06/yyyyy/LIPREM')
insert into #test values ('06/xxxyy/CLUB')
insert into #test values ('06/yyxxx/CLUB')
insert into #test values ('05/xxxxy/LATEMP')


select distinct REVERSE(LEFT(REVERSE(SomeField),CHARINDEX('/',reverse(SomeField))-1)) 
from #test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lorca]

mmmmmmmm...........strange

if i move the '-1' i get an error
'error converting the varchar value 'MERPIL/660000/60'' to a column of data type int.

my column LicType is defitinely varchar(255)
it fails on the first row which is

06/00066/LIPREM

SELECT DISTINCT REVERSE(SUBSTRING(REVERSE(LicType), 1, CHARINDEX('/', REVERSE(LicType) - 1))) AS lictype_extracted
FROM MyTable
ORDER BY lictype_extracted

thanks

 

[SQLDenis]

what about the code I gave you?
try

select distinct REVERSE(LEFT(REVERSE(LicType),CHARINDEX('/',reverse(LicType))-1)) AS lictype_extracted
FROM MyTable
ORDER BY 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

This should also work.

Select Distinct
       ParseName(Replace(LicType, '/', '.'), 1)
From   [!]TableName[/!]

-George

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

 

[SQLDenis]

George, I actually used that myself many times and forgot about it now
That's what you get when there are 50 ways to accomplish something
Star for you

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[philhege]

Ditto on the star; quite an inventive use of that function.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[gmmastros]

A hat trick. Thanks guys.

I'm beginning to feel bad about this, though. I learned the technique from Vongrunt about a year ago.

-George

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

 

[philhege]

Well, it's no different from technology innovation. Just pay Vongrunt 600 mil and continue raking in the stars!

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.