column formatting

Question

Viewing 7 posts - 1 through 6 (of 6 total)

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

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

Since you are only making reference to character positions 1-9, are you sure you mean varchar(100) and not varchar(10)?

Can you give some data examples of what you are expecting to enter, and what you expect to be stored.

eletuw SSCrazy Points: 2464 More actions · Answer 2

The column contains data like this:

11 A10 Axxxxxxxxxxxxxxxxxxxxxxxx

There is a request for me to reformat the column such that the column becomes

11A 10Axxxxxxxxxxxxxxxxxxxxxxxx

I hope this clarifies it. Thanks.

paulhumphris Hall of Fame Points: 3496 More actions · Answer 3

Try this, I think it gives you the results you are looking for...

declare @test-2 table (field varchar(100))

insert into @test-2 (field)

values ('11 A10 Axxxxxxxxxxxxxxxxxxxxxxxx')

select replicate(' ', 4 - len(rtrim(ltrim(left(field, 4))))) + rtrim(ltrim(left(field, 4))) +

substring(field, 5, 1) +

replicate(' ', 4 - len(rtrim(ltrim(substring(field, 6, 4))))) + rtrim(ltrim(substring(field, 6, 4))) +

right(field, len(field) - 9) as result

eletuw SSCrazy Points: 2464 More actions · Answer 4

Thanks, this is likely to help. I am now trying to resolve the error:

Invalid length parameter passed to the substring function.

which occured on line one.

Thanks

paulhumphris Hall of Fame Points: 3496 More actions · Answer 5

The data in your field is obviously less than the number of charaters being search for in the SUBSTRING command or the field is null.

Needs some validation in your query I think.

eletuw SSCrazy Points: 2464 More actions · Answer 6

You are spot on. I have got it sorted.

Thanks a lot for your help.

Regards.