Display values from ntext defined field

Question

Post reply

Display values from ntext defined field

Bruin

SSCrazy

Points: 2091
More actions
November 10, 2022 at 5:26 pm
Go to Answer
#4111049

How do I go about display the values or figuring out what the delimiter. I need to break a part the string into individual fields.
Thanks.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1383 More actions · Answer 1

You don't know the delimiter? It's not predefined/standardized?

STRING_SPLIT function is available in 2016, but would require nvarchar instead of ntext.

ntext has been deprecated since SQL Server 2008, & has limitations -- can you convert to nvarchar?

Are the values unicode? Do the values exceed 4000 characters?

Bruin SSCrazy Points: 2091 More actions · Answer 2

Not a standard delimiter ...

If I can find the delimiter any code I can use to parse the data?

Bruin SSCrazy Points: 2091 More actions · Answer 3

I was able to convert field. I copied table to a 2016 box.

STRING_SPLIT function is available in 2016, but would require nvarchar instead of ntext.

Looks like they are using more than 1 value for delimiter..

any suggestions?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

Read the NTEXT into an NVARCHAR(MAX).
Use a Tally table function to read each character. If it's not the letters A-Z or a-z or 0-9 or common symbols, then it must be a delimiter. Capture that and use it as a delimiter for String_Split.

--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 SSCrazy Points: 2091 More actions · Answer 5

Read the NTEXT into an NVARCHAR(MAX). --> done

It appears that they used 3 characters to delineate the data --> |++

Use a Tally table function to read each character. If it's not the letters A-Z or a-z or 0-9 or common symbols, then it must be a delimiter. Capture that and use it as a delimiter for String_Split.

-- Can I still do this with the 3 character delimiter? Not sure how to do that using String_Split.

Thx.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 6

I am pretty sure you have heard of the word "replace" - use it to replace those 3 chars with another single delimiter that is not used within the text.

and do not use the native string_split - slow and does not return the ordinal of the strings.

search the forums here for the delimitedsplit function that would work faster.

Bruin SSCrazy Points: 2091 More actions · Answer 7

select cast(replace(cast(body as nvarchar(max)),'???','?') as ntext)

from lookuptable2

where

description like '???%'

I used this template and can see that it did indeed did do the replace, but how do I capture individual fields...

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

frederico_fonseca wrote:

do not use the native string_split - slow and does not return the ordinal of the strings.

Funny thing there... I've never actually tested it for performance because I never use it just because of the missing ordinal thing.

[EDIT} Ah... now I remember why I've not tested it for performance. Wayne Sheffield tested it way back when. Here's his article on that subject.

https://www.sqlservercentral.com/articles/splitting-strings-in-sql-server-2016

This reply was modified 2 years, 1 month ago by Jeff Moden. Reason: Added Wayne Sheffield's post

--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)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 9

Bruin wrote:

select cast(replace(cast(body as nvarchar(max)),'???','?') as ntext) from lookuptable2 where description like '???%'
I used this template and can see that it did indeed did do the replace, but how do I capture individual fields...

Bruin wrote:

select cast(replace(cast(body as nvarchar(max)),'???','?') as ntext) from lookuptable2 where description like '???%'
I used this template and can see that it did indeed did do the replace, but how do I capture individual fields...

~~Why the REPLACE??? Are you now saying that you know what the delimiter is?~~

[EDIT] Ah... I missed your other post about the 3 character delimiters and now understand the reason for needing REPLACE

And, to answer the second question there... you've already been told... use a String_Splitter with the delimiter that you've found.

And, remember, we can't see your data so you're pretty much on your own there.

To repeat the steps I laid out...

1. Read the NTEXT into an NVARCHAR(MAX).

2. Use a Tally table function to read each character. If it's not the letters A-Z or a-z or 0-9 or common symbols, then it must be a delimiter. Capture that and use it as a delimiter for String_Split.

While String_Split may be slow ((something I've not confirmed before)) and it has no elemental ordinal in the return, you've not stated that you need to have any sense of order in the "fields" that you return.

--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 Ten Centuries Points: 1383 More actions · Answer 10

frederico_fonseca wrote:
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

Bruin SSCrazy Points: 2091 More actions · Answer 11

Sample data in field:

PartNbr|#@|Machine|#@| ttxx23|#@|plc1|#@| ttxx24|#@|plc2

So it looks like PartNbr|#@|Machine| -- these are the fields

Then | ttxx23| -- when it sees a space data starts

partnbr Machine

ttxx23 plc1

ttxx24 plc2

any suggestions

This reply was modified 2 years, 1 month ago by Bruin.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 12

Bruin wrote:

Sample data in field:
PartNbr|#@|Machine|#@| ttxx23|#@|plc1|#@| ttxx24|#@|plc2
So it looks like PartNbr|#@|Machine| -- these are the fields Then | ttxx23| -- when it sees a space data starts
partnbr Machine ttxx23 plc1 ttxx24 plc2
any suggestions

Sorry... no. I give up.

--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)

David Burrows SSC Guru Points: 65144 More actions · Answer 13

Use delimited split using pipe as delimiter and then use MOD to select 1st and 3rd items

Far away is close at hand in the images of elsewhere.
Anon.

Bruin SSCrazy Points: 2091 More actions · Answer 14

Bruin

SSCrazy

Points: 2091

November 11, 2022 at 7:15 pm

#4111774

could you send a sample of that code?

Thx.