Discussion:
Midstring woes
(too old to reply)
unknown
2009-01-22 19:02:50 UTC
Permalink
I am trying to extract the middle of a column as a computed
field.

table = csmt
column = cio
computed_column = cio_number

Value of cio is "60012345 - Some part of the project"
Value I want from this is "12345"

Please help my foggy memory as to what the correct syntax
would be.

I am using Infomaker 10.5 against a MSSQL 2000 server.

Thanks!
Chris Pollach
2009-01-22 19:08:27 UTC
Permalink
G'day;

May I suggest using the Right ( ), Left ( ) or MID ( ) functions for
that. In your example below, I would use the Right method but in your
original question I would use the MID method. For example to get '12' from
60012345 => MID (filed_name, 4,2).

HTH
--
Regards ... Chris
ISUG - NA RUG Director
http://chrispollach.pbdjmagazine.com
Post by unknown
I am trying to extract the middle of a column as a computed
field.
table = csmt
column = cio
computed_column = cio_number
Value of cio is "60012345 - Some part of the project"
Value I want from this is "12345"
Please help my foggy memory as to what the correct syntax
would be.
I am using Infomaker 10.5 against a MSSQL 2000 server.
Thanks!
unknown
2009-01-22 19:10:04 UTC
Permalink
OK. Not a memory problem...

I just decided to make it a left 9, right 5 problem. I am
getting the same error. Even when I try left 9 only.

sqlstate = s1093
invalid parameter number

I get out of the data window just fine, it is when I try to
preview I get the error.

Thanks!
Post by unknown
I am trying to extract the middle of a column as a
computed field.
table = csmt
column = cio
computed_column = cio_number
Value of cio is "60012345 - Some part of the project"
Value I want from this is "12345"
Please help my foggy memory as to what the correct syntax
would be.
I am using Infomaker 10.5 against a MSSQL 2000 server.
Thanks!
Terry Dykstra [TeamSybase]
2009-01-22 19:11:20 UTC
Permalink
In a report:
mid(cio_number,4,5)

In sqlserver2000 sql:
substring(cio,4,5)
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement
Post by unknown
I am trying to extract the middle of a column as a computed
field.
table = csmt
column = cio
computed_column = cio_number
Value of cio is "60012345 - Some part of the project"
Value I want from this is "12345"
Please help my foggy memory as to what the correct syntax
would be.
I am using Infomaker 10.5 against a MSSQL 2000 server.
Thanks!
unknown
2009-01-22 19:26:46 UTC
Permalink
I used this as computed column then as such:

( SELECT substring(cio,4,5) from csmt ) as cio_number

I get the S1093 error invalid parameter number.
Post by Terry Dykstra [TeamSybase]
mid(cio_number,4,5)
substring(cio,4,5)
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
http://my.isug.com/cgi-bin/1/c/submit_enhancement
to extract the middle of a column as a computed field.
table = csmt
column = cio
computed_column = cio_number
Value of cio is "60012345 - Some part of the project"
Value I want from this is "12345"
Please help my foggy memory as to what the correct
syntax would be.
I am using Infomaker 10.5 against a MSSQL 2000 server.
Thanks!
unknown
2009-01-22 19:29:07 UTC
Permalink
I also tried to filter in the report

left ( csmt_cost_code , 4 ) <> "100." AND csmt_cost_code <>
"NOT COSTED" AND mid(cio_number,4,5) = "40122"

and got the following error:

Expecting STRING expression
Post by unknown
( SELECT substring(cio,4,5) from csmt ) as cio_number
I get the S1093 error invalid parameter number.
Post by Terry Dykstra [TeamSybase]
mid(cio_number,4,5)
substring(cio,4,5)
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
http://my.isug.com/cgi-bin/1/c/submit_enhancement
to extract the middle of a column as a computed field.
table = csmt
column = cio
computed_column = cio_number
Value of cio is "60012345 - Some part of the project"
Value I want from this is "12345"
Please help my foggy memory as to what the correct
syntax would be.
I am using Infomaker 10.5 against a MSSQL 2000 server.
Thanks!
unknown
2009-01-22 19:33:15 UTC
Permalink
Got this to work... forgot to prepend the table name...

Thank you!
Post by unknown
I also tried to filter in the report
left ( csmt_cost_code , 4 ) <> "100." AND csmt_cost_code
<> "NOT COSTED" AND mid(cio_number,4,5) = "40122"
Expecting STRING expression
Post by unknown
( SELECT substring(cio,4,5) from csmt ) as cio_number
I get the S1093 error invalid parameter number.
Post by Terry Dykstra [TeamSybase]
mid(cio_number,4,5)
substring(cio,4,5)
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
http://my.isug.com/cgi-bin/1/c/submit_enhancement
Post by unknown
trying to extract the middle of a column as a
computed field. >
Post by unknown
table = csmt
column = cio
computed_column = cio_number
Value of cio is "60012345 - Some part of the
project" Value I want from this is "12345"
Please help my foggy memory as to what the correct
syntax would be.
I am using Infomaker 10.5 against a MSSQL 2000
server. >
Post by unknown
Thanks!
Loading...