Strip off first and last characters.

Question

Strip off first and last characters.

Erin.

SSCertifiable

Points: 5237
More actions
September 24, 2008 at 1:40 pm

#71988

I am trying to pull a field where if the data value has 8 characters, strip off the first and last character. If data value has 6 characters, leave as is. The data type for this field is varchar(20).
Can anyone help me, I need to get this report out to our Business Analyst.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

a combination of using substring and a where statement will do the job; you might need to tweak the WHERE statemtn...are you sure it's only items that are exactly 8 chracters, or also greater than 8?

Select substring(columnname,2,6)

where len(columnname) = 8

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!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Erin

I am trying to pull a field where if the data value has 8 characters, strip off the first and last character. If data value has 6 characters, leave as is.

Not to be picky, but what should be done if the data length is 7 characters or is that some how or other not possible due to other filtering of the input data by a user application, or prior T-SQL ? Also see Lowell's question about more than 8 characters in length.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Erin. SSCertifiable Points: 5237 More actions · Answer 3

I was told the software use to allow up to 8 characters. However, it was changed from 8 to 6. But you bring up a good point. I need a more dynamic way to pull this field's characters.