How do I split a field

I have site names in this format:

"Buckingham Palace, 00235"

How do I split this field so that my View shows just "Buckingham Palace"? In Crystal Reports I can use Split but I cannot find the equivalent in Microsoft Express SQL
CMChalcraftFinance DirectorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
LowfatspreadConnect With a Mentor Commented:
select left(sitename,charindex(',',sitename)-1) as sitename
from yourtable
where sitename like '%,%'

or

select case when sitename like '%,%' then left(sitename,charindex(',',sitename)-1) else sitename end as sitename
0
 
rajvjaConnect With a Mentor Commented:
Hi
 You can use the following sample. Here the column values are separated by semicolon.
Change the code to include comma.

SELECT Country_Code,
CASE WHEN CHARINDEX(';', Language,n) = 0 THEN SUBSTRING(Language, n, (LEN(Language)-(n-1))) ELSE
SUBSTRING(Language, n, CHARINDEX(';', Language,n) - n) END AS Language
,n + 1 - LEN(REPLACE(LEFT(Language, n), ' ', '' )) AS language_idx
FROM dbo.Query AS P
CROSS JOIN (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Numbers(n)
WHERE SUBSTRING(' ' + Language, n, 1) = ' ' AND SUBSTRING(';' + Language, (CASE WHEN n > 1 THEN n-1 ELSE n END), 1) = ';'
AND n < LEN(Language) + 1
ORDER BY Country_code
0
 
rajvjaConnect With a Mentor Commented:
Or

select substring('Buck Palace, 123',1,charindex(',','Buck palace, 123')-1)
0
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
In SQL you can use CharIndex function to get the index of , and after that use substring to get desire value.
0
 
CMChalcraftFinance DirectorAuthor Commented:
Thank you all for your help. I have used lowfatspread's solution and this work just fine and dandy.

Thanks

Regards

Chris C
0
All Courses

From novice to tech pro — start learning today.