Solved

How do I split a field

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now