I want a String from Position N until End
The poster wanted to replace the first n characters of a column and keep the rest, but didn't know how to specify "from position N to end of string" since the column length varies and a fixed length like substr(col,10,100) truncates. Replies gave several working answers: omit the length argument, as in substr(column_name,10), which defaults to end of string (or use length(column_name)); use subscripting, e.g. 'new_string' || column_name[start,end]; or use the SQL SUBSTRING form, substring(column_name from 10), which was confirmed available as far back as 7.31/9.21.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello - i need your brain... Is there a function that select a column from pos a until max (length of the column) ? I need something like this: select concat("new-string", substr(column_name,10, max-length-of-col )) from mytable; - i have to change the fist n characters of a column. and this: select concat("new-string", substr(column_name,10,100)) from table; is not working because the column can be more than 100 characters long. is there someone with a idea ?
> Is there a function that select a column from pos a until max > (length of the column) ? > I need something like this: > select concat("new-string", substr(column_name,10, max-length-of-col )) > from mytable; > - i have to change the fist n characters of a column. > and this: > select concat("new-string", substr(column_name,10,100)) from table; > is not working because the column can be more than 100 characters long. > is there someone with a idea ? I don't think you need a function. update mytable set column_name = 'new_string' || column_name[{start}, {end}] where...
select concat("new-string", substr(column_name,10)) from my table; will do the trick. Matthias wrote: > Hello - i need your brain... > > Is there a function that select a column from pos a until max > (length of the column) ? > > I need something like this: > > select concat("new-string", substr(column_name,10, max-length-of-col )) > from mytable; > > - i have to change the fist n characters of a column. > > and this: > > select concat("new-string", substr(column_name,10,100)) from table; > > is not working because the column can be more than 100 characters long. > > > is there someone with a idea ?
Because when you use substr(column_name, <integer> ) substr assumes that the ending point is the end of the string. Of course you could also use substr(column_name, <integer>, length(column_name) ) but why bother when the nice people at Informix created the above short-cut. (Thank You, nice people at Informix.) bozon wrote: > select concat("new-string", substr(column_name,10)) from my table; > > will do the trick. > > Matthias wrote: > > Hello - i need your brain... > > > > Is there a function that select a column from pos a until max > > (length of the column) ? > > > > I need something like this: > > > > select concat("new-string", substr(column_name,10, max-length-of-col )) > > from mytable; > > > > - i have to change the fist n characters of a column. > > > > and this: > > > > select concat("new-string", substr(column_name,10,100)) from table; > > > > is not working because the column can be more than 100 characters long. > > > > > > is there someone with a idea ?
Because when you use substr(column_name, <integer> ) substr assumes that the ending point is the end of the string. Of course you could also use substr(column_name, <integer>, length(column_name) ) but why bother when the nice people at Informix created the above short-cut. (Thank You, nice people at Informix.) bozon wrote: > select concat("new-string", substr(column_name,10)) from my table; > > will do the trick. > > Matthias wrote: > > Hello - i need your brain... > > > > Is there a function that select a column from pos a until max > > (length of the column) ? > > > > I need something like this: > > > > select concat("new-string", substr(column_name,10, max-length-of-col )) > > from mytable; > > > > - i have to change the fist n characters of a column. > > > > and this: > > > > select concat("new-string", substr(column_name,10,100)) from table; > > > > is not working because the column can be more than 100 characters long. > > > > > > is there someone with a idea ?
Matthias wrote: > Hello - i need your brain... > > Is there a function that select a column from pos a until max > (length of the column) ? > > I need something like this: > > select concat("new-string", substr(column_name,10, max-length-of-col )) > from mytable; > > - i have to change the fist n characters of a column. > > and this: > > select concat("new-string", substr(column_name,10,100)) from table; > > is not working because the column can be more than 100 characters long. > > > is there someone with a idea ? > Also if you are running IDS 10.00 (always a good idea to post your version and platform info!) you can use the new SUBSTRING function, it also assumes end-of-string if the 'for N-chars' clause is omitted: select concat("new-string", substring(column_name from 10) from mytable; Art S. Kagel
It also works in 9.21. I think it is in version back to 7.31. Art S. Kagel wrote: > Matthias wrote: > > Hello - i need your brain... > > > > Is there a function that select a column from pos a until max > > (length of the column) ? > > > > I need something like this: > > > > select concat("new-string", substr(column_name,10, max-length-of-col )) > > from mytable; > > > > - i have to change the fist n characters of a column. > > > > and this: > > > > select concat("new-string", substr(column_name,10,100)) from table; > > > > is not working because the column can be more than 100 characters long. > > > > > > is there someone with a idea ? > > > > Also if you are running IDS 10.00 (always a good idea to post your version > and platform info!) you can use the new SUBSTRING function, it also assumes > end-of-string if the 'for N-chars' clause is omitted: > > select concat("new-string", substring(column_name from 10) from mytable; > > Art S. Kagel
bozon wrote: > It also works in 9.21. > I think it is in version back to 7.31. Verified. SUBSTRING is in 7.31 so likely was introduced in 9.21 as well. Art S. Kagel > Art S. Kagel wrote: > >>Matthias wrote: >> >>>Hello - i need your brain... >>> >>>Is there a function that select a column from pos a until max >>>(length of the column) ? >>> >>>I need something like this: >>> >>>select concat("new-string", substr(column_name,10, max-length-of-col )) >>>from mytable; >>> >>>- i have to change the fist n characters of a column. >>> >>>and this: >>> >>>select concat("new-string", substr(column_name,10,100)) from table; >>> >>>is not working because the column can be more than 100 characters long. >>> >>> >>>is there someone with a idea ? >>> >> >>Also if you are running IDS 10.00 (always a good idea to post your version >>and platform info!) you can use the new SUBSTRING function, it also assumes >>end-of-string if the 'for N-chars' clause is omitted: >> >>select concat("new-string", substring(column_name from 10) from mytable; >> >>Art S. Kagel > >