SQL quick help

[rh71]

How do I trim data since we have fields that are char(15) and the source is char(60) ? I want to pull only the first 15 chars from the source within the SELECT statement. TRIM seems to want a specific string to cut out.

 

[Descartes]

You need to always tell us what platform, because it's quite different between them. I'm going to assume SQL Server of Access. It's ltrim() and rtrim() for SQL Server, and Trim() for Access.

 

[rh71]

sorry it's DB2.

 

[StageLeft]

LEFT is the function you want, and you can do a quick search on google for the syntax to use!

 

[rh71]

think we found it.. SUBSTR() ... thx anyway

 

[StageLeft]

substr sounds like it would be used to get portions from within a string, whereas left/right just grab from the left or right based on a length. substr will work though.

 

[KLin]

yes, substr() is what you want to only select a portion of a field for query/400

 

[WannaFly]

If only there was a category for programming questions....man, life would be sweet then.

 

[StageLeft]

Originally posted by: WannaFly
If only there was a category for programming questions....man, life would be sweet then.

Pepsi, is that you? 🙂 NObody checks that forum quick enough if you need help pronto!

 

[rh71]

Originally posted by: Skoorb

Originally posted by: WannaFly
If only there was a category for programming questions....man, life would be sweet then.

Pepsi, is that you? 🙂 NObody checks that forum quick enough if you need help pronto!

Yup. It's not like programmers can sit and wait for a response either when trying to debug.

Alright, this sucks... instead of dropping the table and recreating... who knows how to alter the datatype of a column ?

Tried ALTER TABLE tablename ALTER COLUMN columnname SET DATA TYPE CHAR(140) and it won't accept it. :|

 

[StageLeft]

Well it will only alter if the data within it will be compatible with the new type; ie, if you've got a char(15) and the column is full of 15 length strings you can't go down to (8). You can try a varchar type, to allow variable length strings - that may help you.

 

[rh71]

it's completely blank right now... even dropped the view against it first. (as a learning experience, I'm trying to alter instead of drop/recreate)

The error I'm getting is a syntax problem.

(it's take ATOT to work day) 😀

 

[StageLeft]

Originally posted by: rh71
it's completely blank right now... even dropped the view against it first. (as a learning experience, I'm trying to alter instead of drop/recreate)

The error I'm getting is a syntax problem.

(it's take ATOT to work day) 😀

That sucks, that's why I use enterprise manager (GUI) within sql server when I edit tables; saves me having to learn the raw SQL 😀

 

[rh71]

Originally posted by: Skoorb

Originally posted by: rh71
it's completely blank right now... even dropped the view against it first. (as a learning experience, I'm trying to alter instead of drop/recreate)

The error I'm getting is a syntax problem.

(it's take ATOT to work day) 😀

That sucks, that's why I use enterprise manager (GUI) within sql server when I edit tables; saves me having to learn the raw SQL 😀

I would rather not rip on DB2 Control Center, but... 😉

 

[Descartes]

Originally posted by: rh71

Originally posted by: Skoorb

Originally posted by: WannaFly
If only there was a category for programming questions....man, life would be sweet then.

Pepsi, is that you? 🙂 NObody checks that forum quick enough if you need help pronto!

Yup. It's not like programmers can sit and wait for a response either when trying to debug.

Alright, this sucks... instead of dropping the table and recreating... who knows how to alter the datatype of a column ?

Tried ALTER TABLE tablename ALTER COLUMN columnname SET DATA TYPE CHAR(140) and it won't accept it. :|

To be fair, there are hundreds of other more appropriate forums for programming. There are mailing lists, news groups, forums, etc.

Your syntax looks right from what I can tell. Can you post the actual SQL statement?

 

[rh71]

i gave up and dropped the damned thing. 😉

 

[SarcasticDwarf]

DB2?

*runs like hell in the other direction*