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

Posted on 2013-05-23
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
Question by:MIKE
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
LVL 23

Expert Comment

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 @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

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

LVL 23

Expert Comment

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

USE tempdb

CREATE FUNCTION dbo.city_from_hospital_name
(@instr VARCHAR(1000))
	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);

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

Open in new window

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Expert Comment

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]
LVL 32

Expert Comment

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

Accepted Solution

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:

    LEFT(city_and_state, CHARINDEX(',', city_and_state) - 1) AS city
    SELECT 'Adventist Health System - Winter Park, FL' AS string UNION ALL
    SELECT 'Baptist Hospital - Pensacola, FL'
) AS test_data
    --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

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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