Solved

SQL Syntax to pull part of String Field back..? (CharIndex..?)

Posted on 2013-05-23
6
512 Views
Last Modified: 2013-05-29
My data field looks like this:


"Adventist Health System - Winter Park, FL"

This is a single String/Text Field and is consistent with the " - " and the ", " between the HospitalName and CityName, and the CityName and StateCode. All entries in this field follow this same format:  HospitalName " - " CityName ", " StateCode

I have a requirement to pull ONLY the CITYNAME from the above string field.

So that would be I need to pull the TEXT between the " - " and the ", " 

I need the function and syntax to use....THANKS
0
Comment
Question by:MIKE
6 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39191897
You want PATINDEX and SUBSTRING.  I broke this down into more steps than necessary, just so you can understand it better.

DECLARE @str VARCHAR(100) = 'Adventist Health System - Winter Park, FL';
DECLARE @city VARCHAR(100);

DECLARE @sep1loc INT = PATINDEX('% - %', @str) + 3;
DECLARE @sep2loc INT = PATINDEX('%, %', @str);
DECLARE @citystrlen INT = @sep2loc - @sep1loc;
SELECT @sep1loc, @sep2loc, DATALENGTH(@str), @citystrlen
SELECT SUBSTRING(@str, @sep1loc, @citystrlen);

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39191899
the below one should help
declare @t varchar(200) 
set @t = 'Adventist Health System - Winter Park, FL'
select substring(@t,charindex('-',@t)+1,charindex(',',@t)-charindex('-',@t)-1)

Open in new window

0
 
LVL 23

Expert Comment

by:nemws1
ID: 39191913
Did you want this in a function?  Easy enough.

USE tempdb
GO

CREATE FUNCTION dbo.city_from_hospital_name
(@instr VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @sep1loc INT = PATINDEX('% - %', @instr) + 3;
	DECLARE @sep2loc INT = PATINDEX('%, %', @instr);
	DECLARE @citystrlen INT = @sep2loc - @sep1loc;
	-- SELECT @sep1loc, @sep2loc, DATALENGTH(@str), @citystrlen
	RETURN SUBSTRING(@instr, @sep1loc, @citystrlen);
END
GO

DECLARE @str VARCHAR(100) = 'Adventist Health System - Winter Park, FL';
SELECT dbo.city_from_hospital_name(@str);

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Expert Comment

by:dbote
ID: 39191998
If you don't want to you variables or functions here is a straight SQL example depending on the data you may have to play with the string manipulation.  

select 'Adventist Health System - Winter Park, FL' [Hospital],
      charindex('-','Adventist Health System - Winter Park, FL') [DashStartPosition],
      charindex(', ','Adventist Health System - Winter Park, FL') [StateStartPosition],
      ltrim(substring('Adventist Health System - Winter Park, FL',charindex('-','Adventist Health System - Winter Park, FL') +1,(charindex(', ','Adventist Health System - Winter Park, FL')-1) - (charindex('-','Adventist Health System - Winter Park, FL')))) [CityName]
0
 
LVL 32

Expert Comment

by:awking00
ID: 39192060
select substring(datafield,charindex('-',datafield) + 2,charindex(',',datafield) - charindex('-',datafield) - 2)
from yourtable;
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39192833
A CROSS APPLY makes the code so much easier to read, follow and maintain; and often prevents having to repeat CHARINDEXes and other functions:


SELECT
    LEFT(city_and_state, CHARINDEX(',', city_and_state) - 1) AS city
FROM (
    SELECT 'Adventist Health System - Winter Park, FL' AS string UNION ALL
    SELECT 'Baptist Hospital - Pensacola, FL'
) AS test_data
CROSS APPLY (
    --strip the orig 'name - city, st' string in the table to just 'city, st'
    SELECT SUBSTRING(string, CHARINDEX('-', string) + 2, 200) AS city_and_state
) AS ca1
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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

810 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