Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

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

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
MIKE
Asked:
MIKE
1 Solution
 
nemws1Database AdministratorCommented:
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
 
Surendra NathTechnology LeadCommented:
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
 
nemws1Database AdministratorCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dboteCommented:
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
 
awking00Commented:
select substring(datafield,charindex('-',datafield) + 2,charindex(',',datafield) - charindex('-',datafield) - 2)
from yourtable;
0
 
Scott PletcherSenior DBACommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now