September 14, 2017 at 10:47 am
Hi,
I have string with streetname ,city and state in the following folrmat.
I need to split this into three diff values.
Wenday Ln [frisco, tx)
Street=Wenday Ln
City=Frisco
State=tx
How can I do this with the two different delimeters.
Thanks.
September 14, 2017 at 10:53 am
Please post consumable sample data, create table statement with insert statements. Also check out the new String_Split() function in SQL 2016.
September 14, 2017 at 11:13 am
sql_2005_fan - Thursday, September 14, 2017 10:47 AMHi,I have string with streetname ,city and state in the following folrmat.
I need to split this into three diff values.
Wenday Ln [frisco, tx)
Street=Wenday Ln
City=Frisco
State=tx
How can I do this with the two different delimeters.Thanks.
Is that a left square bracket and a right round bracket? The code is relatively simple using charindex and substring functions.
September 14, 2017 at 11:43 am
sql_2005_fan - Thursday, September 14, 2017 10:47 AMHi,I have string with streetname ,city and state in the following folrmat.
I need to split this into three diff values.
Wenday Ln [frisco, tx)
Street=Wenday Ln
City=Frisco
State=tx
How can I do this with the two different delimeters.Thanks.
This is still relatively easy, using a string splitter function. Jeff Moden has one posted on this site under the Articles section, where you can search for "Tally Oh", and you'll find it. The name of that function is DelimitedSplit8K. Here's some sample code:CREATE TABLE #YourTable (
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
YourFieldName varchar(25)
);
INSERT INTO #YourTable (YourFieldName)
SELECT 'Wenday Ln [frisco, tx)'
SELECT YT.ID,
MAX(CASE S1.ItemNumber WHEN 1 THEN S1.Item ELSE NULL END) AS Street,
MAX(S2.Item) AS City,
MAX(REPLACE(S3.Item , ')', '')) AS [State]
FROM #YourTable AS YT
CROSS APPLY dbo.DelimitedSplit8K(YT.YourFieldName, '[') AS S1
OUTER APPLY (SELECT S.Item FROM dbo.DelimitedSplit8K(S1.Item, ',') AS S WHERE S1.ItemNumber = 2 AND S.ItemNumber = 1) AS S2
OUTER APPLY (SELECT S.Item FROM dbo.DelimitedSplit8K(S1.Item, ',') AS S WHERE S1.ItemNumber = 2 AND S.ItemNumber = 2) AS S3
GROUP BY YT.ID;
DROP TABLE #YourTable;
You can find the code for the splitter at the bottom of the article in a zip file, here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 14, 2017 at 12:08 pm
For this, I might keep a more straightforward approach.
SELECT LEFT( YourFieldName, CHARINDEX('[', YourFieldName + '[')-1),
SUBSTRING( YourFieldName, CHARINDEX('[', YourFieldName + '[')+1, CHARINDEX(',', YourFieldName + ',', CHARINDEX('[', YourFieldName + '[')) - CHARINDEX('[', YourFieldName + '[')-1),
SUBSTRING( YourFieldName, CHARINDEX(',', YourFieldName + ',', CHARINDEX('[', YourFieldName + '[')) + 2, 2)
FROM #YourTable;
EDIT:
If you want an improved readability:
SELECT LEFT( YourFieldName, Street.Delimeter-1),
SUBSTRING( YourFieldName, Street.Delimeter + 1, City.Delimeter - Street.Delimeter - 1),
SUBSTRING( YourFieldName, City.Delimeter + 2, 2)
FROM #YourTable
CROSS APPLY (SELECT CHARINDEX('[', YourFieldName + '[')) Street(Delimeter)
CROSS APPLY (SELECT CHARINDEX(',', YourFieldName + ',', Street.Delimeter)) City(Delimeter);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply