December 6, 2016 at 8:49 am
Good day gurus!!!
I have a string in a varchar(max) field where I need to extract out only certain values. How would I go about getting everything out of the string below that starts with "m_". Note, there are two values that meet the criteria (m_general, m_solitaire)
app_education, apple, general_17, ios_version_9_3_5, m_general, m_solitaire, phone, solitaire, solitaire_8
Thank you!!
December 6, 2016 at 9:18 am
Read Jeff's article on DelimitedSplit8K[/url], which contains a download that has the complete DelimitedSplit8K code. (Read the article, and the link is... <should I tell?> ) Once you have that, splitting is not too hard. Took me a couple of times, because I had to remove the spaces... If you read the article the code will make sense. Jeff explains it all. The only part I added was the WHERE clause, to filter for only the values starting with 'm_'.
CREATE TABLE #Test( ID INT IDENTITY, TextField VARCHAR(200) );
GO
INSERT INTO #Test(TextField) VALUES ('app_education, apple, general_17, ios_version_9_3_5, m_general, m_solitaire, phone, solitaire, solitaire_8');
SELECT ItemNumber, Item
FROM (
SELECT split.ItemNumber, Item = split.Item
FROM #Test test
CROSS APPLY dbo.DelimitedSplit8k(test.TextField,',') split
) x
WHERE LTRIM(Item) LIKE 'm_%';
December 6, 2016 at 9:19 am
eshaw (12/6/2016)
Good day gurus!!!I have a string in a varchar(max) field where I need to extract out only certain values. How would I go about getting everything out of the string below that starts with "m_". Note, there are two values that meet the criteria (m_general, m_solitaire)
app_education, apple, general_17, ios_version_9_3_5, m_general, m_solitaire, phone, solitaire, solitaire_8
Thank you!!
Truly the best way would be to stop storing delimited data like this. It violates 1NF and causes unbelievable amounts of anguish.
However in the real world this isn't always possible. You are going to need to split these delimited lists in order to do anything with them. Do you really need varchar(max)? Are the values really that long? If you can use varchar(8000) you can use the Jeff Moden splitter, you can find it by following the link in my signature about splitting strings. If however you really have to use varchar(max) you will need a different splitter. Here is a great article with some alternatives (but they are not as fast as Jeff Moden's splitter), and they don't return the ordinal position which can sometimes be a deal break. https://sqlperformance.com/2012/07/t-sql-queries/split-strings
_______________________________________________________________
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/
December 6, 2016 at 9:28 am
eshaw (12/6/2016)
Good day gurus!!!I have a string in a varchar(max) field where I need to extract out only certain values. How would I go about getting everything out of the string below that starts with "m_". Note, there are two values that meet the criteria (m_general, m_solitaire)
app_education, apple, general_17, ios_version_9_3_5, m_general, m_solitaire, phone, solitaire, solitaire_8
Thank you!!
How long is the maximum length of your varchar(max) field? If it's 8,000 or less, you can probably just use a string split function that's been referred to here in the forums repeatedly: DelimitedSplit8K
It depends on data being varchar up to 8,000 characters, and requires there be a consistent delimiter between each element. I can't recall if there's a varchar(max) version. You can find the max length of your field's data as follows:
SELECT MAX(LEN(VarChar_Field))
FROM YourTable;
You could then split the strings as follows:
SELECT T.PrimaryKey_Field, S.Item AS THE_ELEMENT
FROM YourTable AS T
CROSS APPLY dbo.DelimitedSplit8K(VarChar_Field, ',') AS S
WHERE S.Item LIKE 'm_%'
ORDER BY T.PrimaryKey_Field, S.ItemNumber;
You may find that because individual elements have spaces preceding the values that you end up needing to change the WHERE clause to account for the spaces, but hopefully, you're heading in the right direction.
Another consideration here is that if there's a rather small number of elements per field value that need to be selected, and that there's a large number of fields, that performance isn't great, but then the alternative could be a lot uglier unless you know the maximum number of possible qualifying elements.
The DelimitedSplit8K function can be found here:
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
Please read the article, as it goes into plenty of detail, and understanding that is going to be quite useful.
EDIT: fixed the link
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 6, 2016 at 9:52 am
Thank you all for the quick reply. This worked like a charm!!!!
December 6, 2016 at 10:11 am
If you really do need to do this on a MAX data type, performance is going to suffer. It's best to do it on a Varchar(8000) instead. You don't have to change the data itself, just the data type, to suffer the performance penalty.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply