Solved

How do I split a field

Posted on 2011-02-11
5
249 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:CMChalcraft
5 Comments
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 200 total points
ID: 34870008
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
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 200 total points
ID: 34870016
Or

select substring('Buck Palace, 123',1,charindex(',','Buck palace, 123')-1)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 200 total points
ID: 34870406
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
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 100 total points
ID: 34870684
In SQL you can use CharIndex function to get the index of , and after that use substring to get desire value.
0
 

Author Comment

by:CMChalcraft
ID: 34871491
Thank you all for your help. I have used lowfatspread's solution and this work just fine and dandy.

Thanks

Regards

Chris C
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question