May 19, 2014 at 2:26 pm
A report developer came to me with the following method of parsing out a string...the data comes from a table that is LEFT joined upon and looks like the example string below (the number of spaces between each data element can be different i.e. (2 spaces, 3 spaces, 1 space, etc)):DECLARE @string AS varchar(40)
SET @string = ' OKALOOSA AIRPORT ABC VALPRAISO FL '
/*State*/
SELECT
REVERSE(LEFT(REPLACE(REPLACE(REPLACE(REVERSE(LTRIM(RTRIM(@string))), ' ', '<>'), '><', ''), '<>', '*'),
CHARINDEX('*', REPLACE(REPLACE(REPLACE(REVERSE(LTRIM(RTRIM(@string))), ' ', '<>'), '><', ''), '<>', '*')) - 1))
/*City*/
SELECT
REPLACE((REVERSE(LEFT(REPLACE(REPLACE(REPLACE(REVERSE(LTRIM(RTRIM(@string))), ' ', '<>'), '><', ''), '<>', '*'),
CHARINDEX('*', REPLACE(REPLACE(REPLACE(REVERSE(LTRIM(RTRIM(@string))), ' ', '<>'), '><', ''), '<>', '*'),
CHARINDEX('*', REPLACE(REPLACE(REPLACE(REVERSE(LTRIM(RTRIM(@string))), ' ', '<>'), '><', ''), '<>', '*'))
+ 1) - 1))),
'*' + REVERSE(LEFT(REPLACE(REPLACE(REPLACE(REVERSE(LTRIM(RTRIM(@string))), ' ', '<>'), '><', ''), '<>', '*'),
CHARINDEX('*', REPLACE(REPLACE(REPLACE(REVERSE(LTRIM(RTRIM(@string))), ' ', '<>'), '><', ''), '<>', '*')) - 1)),
'')
Is there a better way? My first thought was to create a function to replace the spaces with a single space, then parse out the last 2 fields - but surely there's got to be a better less-CPU intensive way of doing this - suggestions?
The desired output is:
VALPRAISO, FL
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 19, 2014 at 2:41 pm
Yes you can replace multiple spaces with a single space much easier than this.
http://qa.sqlservercentral.com/articles/T-SQL/68378/[/url]
It looks like you have a mountain of nested replaces in here for a number of other scenarios. It is hard to distinguish which of those are required and which are used in the replacing of multiple spaces.
If you take this one step further and assume you are going to get a variable number of spaces you could then use the DelimitedSplit8K function like this.
DECLARE @string AS varchar(40)
SET @string = ' OKALOOSA AIRPORT ABC VALPRAISO FL '
SELECT @string = LTRIM(
REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(@string))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')) --AS CleanString --Changes the remaining X's to nothing
WHERE CHARINDEX(' ',@string) > 0;
with SortedValues as
(
select top 2 *, ROW_NUMBER() over(order by ItemNumber desc) as RowNum
from dbo.DelimitedSplit8K(@string, ' ')
order by ItemNumber desc
)
select MAX(case when RowNum = 2 then Item end) + ', ' + MAX(case when RowNum = 1 then Item end) as MyResult
from SortedValues
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2014 at 10:44 pm
This would work, however wedging it into the current query looks like an dreadful task. For the basis of my example I used @String to parse, but in relation to the actual query, @String is a column from a massive query with about 20 table joins...
Consider something like (as an example only):
SELECT col1 - col50, FieldNeededToParse,
LoadsOfAggregatedColums
FROM Table1 t1,
INNER JOIN Table2 t2 ON t1.col = t2.col
...
...
...
INNER JOIN Table20 t20 ON t15.col = t20.col
LEFT JOIN TableWithHorribleFieldToParse crap ON crap.col = t1.col
WHERE
MassiveListOfColumns = Convoluted logic
GROUP BY NonAggregatedColumns
I guess I can try to wrap my head around it, but there has to be another way?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 20, 2014 at 11:08 am
Sean Lange (5/19/2014)
Yes you can replace multiple spaces with a single space much easier than this.http://qa.sqlservercentral.com/articles/T-SQL/68378/[/url]
It looks like you have a mountain of nested replaces in here for a number of other scenarios. It is hard to distinguish which of those are required and which are used in the replacing of multiple spaces.
If you take this one step further and assume you are going to get a variable number of spaces you could then use the DelimitedSplit8K function like this.
DECLARE @string AS varchar(40)
SET @string = ' OKALOOSA AIRPORT ABC VALPRAISO FL '
SELECT @string = LTRIM(
REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(@string))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')) --AS CleanString --Changes the remaining X's to nothing
WHERE CHARINDEX(' ',@string) > 0;
with SortedValues as
(
select top 2 *, ROW_NUMBER() over(order by ItemNumber desc) as RowNum
from dbo.DelimitedSplit8K(@string, ' ')
order by ItemNumber desc
)
select MAX(case when RowNum = 2 then Item end) + ', ' + MAX(case when RowNum = 1 then Item end) as MyResult
from SortedValues
Going off what Sean did, and not sure if this is the most performant way, I used a single CTE at the top to try to get it into a format where you could easily plug it into your existing query.
You can select through "Select * from cte_deCrapify" to see how it's presenting the data, and then see what I attempted to do with your query. Maybe you can play around with this formatting and see if it will work better.
Good luck!
DECLARE @CrapTable TABLE (col int NOT NULL, CrapValue varchar(600) NOT NULL)
INSERT INTO @CrapTable
SELECT 24, ' OKALOOSA AIRPORT ABC VALPRAISO FL '
UNION ALL
SELECT 38, ' TUSCALOOSA AIRFIELD XYZ SOMEWHERE AL'
UNION ALL
SELECT 1978, ' PLACE HOLDER NULL CHICAGO IL '
UNION ALL
SELECT 294959, ' HOBBY AIRPORT PDQ HOUSTON TX '
;WITH cte_DeCrapify
AS
(
SELECT C.col, C.TrimmedCrapValue, CD.Item, CD.RowNum
FROM
(
SELECT col
, TrimmedCrapValue = LTRIM(
REPLACE(
REPLACE(
REPLACE(
LTRIM(RTRIM(CrapValue))
,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model
,CHAR(7)+' ','') --Changes the XO model to nothing
,CHAR(7),'')) --Changes the remaining X's to nothing
FROM @CrapTable
WHERE CHARINDEX(' ',CrapValue) > 0
)
as C
CROSS APPLY
(
SELECT TOP 2 C.col
, X.Item
, ROW_NUMBER() OVER (
ORDER BY X.ItemNumber DESC
) AS RowNum
FROM dbo.DelimitedSplit8K(C.TrimmedCrapValue, ' ') AS X
ORDER BY X.ItemNumber DESC
) AS CD
)
Select * from cte_DeCrapify
-- ATTEMPT TO JOIN INTO YOUR QUERY IN AN EASIER WAY
SELECT col1
-- more columns
, col50
, CleanTable.MyResult
, LoadsOfAggregatedColums
FROM Table1 t1
INNER JOIN Table2 AS t2 ON t1.col = t2.col
-- more joins
INNER JOIN Table20 AS t20 ON t15.col = t20.col
LEFT JOIN (SELECT col, MAX(CASE
WHEN RowNum = 2
THEN Item
END) + ', ' + MAX(CASE
WHEN RowNum = 1
THEN Item
END) AS MyResult FROM cte_deCrapify
) as CleanTable ON CleanTable.col = t1.col
WHERE MassiveListOfColumns = Convolutedlogic
GROUP BY NonAggregatedColumns
May 20, 2014 at 11:25 am
A slight modification to Sean's code to avoid the REPLACEs.
DECLARE @string AS varchar(40)
SET @string = ' OKALOOSA AIRPORT ABC VALPRAISO FL ';
with SortedValues as
(
select top 2 *, ROW_NUMBER() over(order by ItemNumber desc) as RowNum
from dbo.DelimitedSplit8K(@string, ' ')
WHERE Item > ''
order by ItemNumber desc
)
select MAX(case when RowNum = 2 then Item end) + ', ' + MAX(case when RowNum = 1 then Item end) as MyResult
from SortedValues
May 20, 2014 at 11:28 am
Thanks for the suggestions guys! I'll take a deeper look over the next day or two and see what I can come up with!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply