Charindex

I have atable where i store visitor data (from a website).
In table i have a column LanguageData
Typical entries are:
no
en-us
no,en-us
en; en-us; no
...
So i am trying to do a select where i remove every thing after the ,.
Like this:
LEFT(LanguageData, CHARINDEX(',',
REPLACE(RTRIM(LTRIM(LanguageData)), ';', ',')))
That result in
no,
en,
So i do not get:
no
en-us
no
en
Is there a way i can do a select so that i only get languagecode or the
first languagecode where the user has many (like no, en-us)
I am trying to do this so that i can join it to a table later and show the
full language name.
Best regards
TrondHi
Hi
May be one of
SELECT CASE WHEN Length > 1 THEN LEFT([language],Length-1)
ELSE [language]
END AS [language]
FROM ( SELECT CHARINDEX(',',REPLACE([language],';',','
)) AS LENGTH,
[language]
FROM (
SELECT 'no' as [language]
UNION ALL SELECT 'en-us'
UNION ALL SELECT 'no,en-us'
UNION ALL SELECT 'en; en-us; no' ) A ) B
or
SELECT LEFT([language],ISNULL(Length-1,LEN([Language]))) AS [language]
FROM (
SELECT NULLIF(CHARINDEX(',',REPLACE([language],
';',',')),0) AS LENGTH,
[language]
FROM (
SELECT 'no' as [language]
UNION ALL SELECT 'en-us'
UNION ALL SELECT 'no,en-us'
UNION ALL SELECT 'en; en-us; no' ) A ) B
You may also want to check out:
http://www.users.drew.edu/skass/sql...unction.sql.txt
John
"Trond" <thoiberg@.broadpark.no> wrote in message
news:42629435$1@.news.broadpark.no...
>I have atable where i store visitor data (from a website).
> In table i have a column LanguageData
> Typical entries are:
> no
> en-us
> no,en-us
> en; en-us; no
> ...
> So i am trying to do a select where i remove every thing after the ,.
> Like this:
> LEFT(LanguageData, CHARINDEX(',',
> REPLACE(RTRIM(LTRIM(LanguageData)), ';', ',')))
> That result in
> no,
> en,
> So i do not get:
> no
> en-us
> no
> en
> Is there a way i can do a select so that i only get languagecode or the
> first languagecode where the user has many (like no, en-us)
> I am trying to do this so that i can join it to a table later and show the
> full language name.
> Best regards
> Trond
>