Solved

How do I split a field

Posted on 2011-02-11
5
244 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record open by another user 6 58
Impove long SQL Stored Procedure Performance 14 75
SQL Server 2012 express 24 37
Table create permissions on SQL Server 2005 9 41
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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