SQl Query

Can you modify the length of a column for a view?

It seems that the view is inheriting the column parameters from the table it's querying, which it should.
LVL 1
isamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Najam UddinCommented:
Not directly but with Cast you can tweak it.

SELECT cast(orginal as varchar(updatedLength)) .....

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>It seems that the view is inheriting the column parameters from the table it's querying
Correct.

>Can you modify the length of a column for a view?
Tell us why you want to, as the column data type is already sufficient for the data that's in it.
0
Jose TorresSenior SQL Server DBACommented:
I you are looking to pad the results to a predetermined length you could cast it from say varchar to char.
But as Jim points out it would be easier to understand what you are trying to achieve in order to give you a more precise answer.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

isamesAuthor Commented:
In my BI software I have linked a two views by a column called Group_Number. When I run the BI worksheet that uses these views, I get an error "Invalid Length Parameters passed to the left"
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please post the T-SQL of your view, then run, then when the error displays double-click on the error message and watch the cursor jump to the offending line, and tell us what line that is.
0
PortletPaulfreelancerCommented:
Somewhere in your TSQL you are using LEFT() or SUBSTRING() and probably CHARINDEX()

e.g.

LEFT([some_string], CHARINDEX('X', [some_string]) - 1)

Now as long as [some_string] does actually contain 'X' this would be fine and dandy

BUT is  [some_string] does NOT contain 'X'  then the LEFT() function is trying to do this:

LEFT([some_string], -1)

and that minus one position cannot exist, so you get that error message.

There is a simple solution: ADD 'X' to the string inside the CHARINDEX.


LEFT([some_string], CHARINDEX('X', [some_string] + 'X') - 1)

Now if X is missing from the string you will still get the entire string returned (without the added 'X')

X of course could be any character or set of characters.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.