Find variable length text string

Question

Post reply

Find variable length text string

Howard C. BAchtel-438731

SSCrazy

Points: 2384
More actions
June 9, 2011 at 6:28 am

#241051

I have a variable length text field that is often populated with a string of constant charachters that represent no informaiton (e.g. "__________________"). The length of the underline (in this example) varies from row to row. A sample of two subject rows is below:
2055611629_________________________________________________49
2055611636_______________________________________________________________________71
In the first record (#29) the field length is 49 posiitons completely populated with an underline. The second record (#36) has a field length of 71, also completely populated with an underline.
I would like to identify rows that have this condition to delete from the table.
I would appreciate any suggestions.
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

Howard just finding rows with lots of underscores, or are the underscores a placeholder for variable length data?

this finds them easily, for example:

With mySampleData(TheField)

AS

(

SELECT '20556116 29 _________________________________________________ 49' UNION ALL

SELECT '20556116 36 _______________________________________________________________________ 71' UNION ALL

SELECT '20556116 29 _ 49' UNION ALL

SELECT '20556116 36 71'

)

SELECT * FROM mySampleData

--there must be at least Two underscores

WHERE datalength(TheField) - datalength(REPLACE(TheField,'_','')) > 2

did you want to delete the row of data with this condition, or just the underscores within the data?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 2

Thanks.

I would like to delete the rows where this column is completely populated with the underscores. There are rows that have underscores in this column along with other meaningful data. I do not want to delete those rows and would prefer not to do anything wth the underscores in that row.

My challenge is to find the rows where the column is completely the underscore and be able to delete that row.

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 3

Forgot to include the actual query that may have helped.

Select

TIU_DOCUMENT_8925,

IEN,

REPORT_TEXT,

LEN(REPORT_TEXT) as length

From Vista2.dbo.TIU_ReportText0

Where TIU_DOCUMENT_8925 = 20556116

and IEN in (29,36)

--

The result has the last column as the calculated length of the row.

20556116 29 _________________________________________________ 49

20556116 36 _______________________________________________________________________ 71

Mike John SSCertifiable Points: 7186 More actions · Answer 4

Something like:

select

from ttt

where LEN(replace(REPORT_TEXT,'_','')) = 0

ie replace will replace them all with a zero-length string - if the result is zero length the column was entirely underscores.

This may not be fast on a large volume, but it sounds like you have a one-off jo so that may not matter

Mike

Howard C. BAchtel-438731 SSCrazy Points: 2384 More actions · Answer 5

Thanks to all. I have tried this and it works fine. Actually this is potentially more than a one of, but the overhead to address this at the table creation/append will far outweigh the overhead of resulting record volumn and end user usage many times.

PhilPacha Hall of Fame Points: 3645 More actions · Answer 6

Unfortunately, Mike John's SQL will also return 0 if there are any spaces in the column. This can be solved by replacing LEN with DATALENGTH.

Steve Jones - SSC Editor SSC Guru Points: 734489 More actions · Answer 7

Be careful of using LEN, as noted by Philpacha.

Datalength is much better: http://msdn.microsoft.com/en-us/library/ms173486.aspx

Mike John SSCertifiable Points: 7186 More actions · Answer 8

Good catch thanks gents.

To be totally correct though it only returns 0 if ALL other characters are spaces.

Mike