Display values from ntext defined field
-
Duplicate post deleted...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Bruin wrote:
could you send a sample of that code?
Thx.
How many characters are in the column that you want to split? I ask because, since your column started off as NTEXT, you'll need to use DelimitedSplitN4K, which is limited to only 4000 characters.
Also, you've not answer the implied question about just using the STRING_SPLIT() function... do you actually need to know the ordinal position of each item that has been split out or not? If not, just use STRING_SPLIT() instead.
If you don't know how to use STRING_SPLIT() for such a thing, please do a search on "STRING_SPLIT() (T-SQL)" to find the MS documentation on the function.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
sample data = CREATE TABLE and INSERT scripts, so we have a table of data to work with. Or maybe you really don't want any help?
-
If I do a len(body) it tells me 194245
Thats where I'm struggling based on the sample data I sent not sure how to invoke STRING_SPLIT()
Thanks.
-
they don't want us to help. they want us to do the full code for them for free.
based on the sample data string_split() will not work as it lacks the ordinal position.
DelimitedSplitN4K may not work either if the contents of that ntext are greater than 4k (they may not be though so it should be checked by the OP)
but if they are bigger it can still be done by splitting the contents into blocks of 4k (taking in consideration the need to keep the blocks around each 4k mark together so blocs may need to be slightly smaller than 4k (again easy enough to do through the use of either temp tables or outer applies BEFORE the final split).
Alternatively and if volumes of data aren't that big a version of the DelimitedSplitN4K changed to NMAX can also be created by the OP to deal with this data.
But that will be way above the OP skill level.
- Bruin wrote:
If I do a len(body) it tells me 194245
Thats where I'm struggling based on the sample data I sent not sure how to invoke STRING_SPLIT()
Thanks.
is that size on the TEXT field or after you converted to nvarchar(max).
and as for examples ... there are lots and lots of examples on the net - including some on these forums.
-
If you use this nvarchar(MAX) STRING_SPLIT function
You can do it with the following code:
with cte as (select 'PartNbr|#@|Machine|#@| ttxx23|#@|plc1|#@| ttxx24|#@|plc2' Value)
,cte2 as (select x.value, x.position from cte
cross apply dbo.string_split(Value,'|#@|') x)
,cte3 as (select Position, Value Col1, lead(value) over (order by position) as Col2 from cte2)
select Col1, Col2
from cte3
where position%2=1
order by position;
-
This is doing exactly what I was looking for but when I ran it there are 2 more columns that need added
select 'PartNbr|#@|Machine|#@|CCode|#@|Cost
How can those be introduced into current code...
thanks!!! for all replies
-
with cte as (select 'PartNbr|#@|Machine|#@|CCode|#@|Cost|#@| ttxx23|#@|plc1|#@| ttxx24|#@|plc2' Value)
,cte2 as (select x.value, x.position from cte
cross apply dbo.string_split(Value,'|#@|') x)
,cte3 as (select position,
Value Col1,
lead(value) over (order by position) as Col2,
lead(value, 2) over (order by position) as Col3,
lead(value, 3) over (order by position) as Col4
from cte2)
select Col1, Col2, Col3, Col4
from cte3
where position%4=1
order by position; -
Many Thanks to you!!!!!!
-
Thanks for ALL who replied ... everyone you are awesome many great solutions are always supplied and great detail
behind the solutions and examples.
-
If you would post more detailed information (like you've been asked before), it 1) wouldn't take so long and 2) people wouldn't give up on you.
I'll point you to the article at the first link in my signature line below.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - ratbak wrote:
frederico_fonseca wrote: drift boss
do not use the native string_split - slow and does not return the ordinal of the strings.
Aaron Bertrand's tests from 2016 showed exceptionally good performance: https://sqlperformance.com/2016/03/sql-server-2016/string-split
Thanks for your sharing
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply