Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1200
  • Last Modified:

Left substring in Sybase

I have data in a column that is declared as varchar(20) and the data looks as follows:   123456.pdf
The number of characters before the .pdf can range from 6-10, so I need to grab all data to the left of the period.
I want to put this data into a new column in a different table truncating the ".pdf" part of the string.

My question is basically this:  How do I truncate the data to the left of the .pdf?
0
jmp02008
Asked:
jmp02008
1 Solution
 
sathyagiriCommented:
In ORACLE it would be

 select substr('123456.pdf',1,instr('123456.pdf','.pdf') -1 ) from <tname>

Not sure if SYBASE has a INSTR function
0
 
Jim P.Commented:
SELECT  LEFT(MyField, LENGTH(MyField)-4)
FROM MyTableName
0
 
jmp02008Author Commented:
Ok this is what I got to work, but I'm getting an error when trying to insert it into a new table.

insert into table2 select substring(column_name, 1, charindex('.', column_name)-1) from table1

The table's are identical with respect to column name, type, and size.  But this is my error:
Msg 213, Level 16, State 4
Insert error: column name or number of supplied values does not match table definition.

any suggestions?



I can't use Left because I don't know the length of the string, it could be anywhere between 6-10 characters.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim P.Commented:
insert into table2 (column_name)
select substring(column_name, 1, charindex('.', column_name)-1) from table1


 LENGTH(MyField)-4 <-- If you know they will end in .pdf this will determine the length of the string and take off the last 4 characters.
0
 
jrb1Commented:
How many columns in your tables? If more than one, you should have

insert into table2 (col1, col2, col3)
select substring(column_name, 1, charindex('.', column_name)-1), col2, col3
from table1
0
 
Jim P.Commented:
jrb1,

http://www.experts-exchange.com/Databases/Q_21900951.html#16995091

At the above post you can see that the Asker is only working on one column and I addressed it in my follow up post to that.

0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now