Solved

How do I split a field

Posted on 2011-02-11
5
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
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…

730 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