Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

SQL SQL Statement Need Help, EDI

views
     
TSkinni
post Nov 18 2015, 12:50 PM, updated 9y ago

Casual
***
Junior Member
320 posts

Joined: Nov 2009
From: Kajang/KL


Hi there, I am currently working out with EDI but unfortunately I am stuck at the "Filler" requirement. In previous works, I used to type manually with the desired length by using blank space like below:
", '(space * 100) " +
space *(100) " " +
" space * (100) ' AS filler " +

But the problem now, current filler required 9k digits!! Which really shock me as I might not wish to type 9000 blank space. I would like to ask is there any method or statement that I can specific a length for specific column during select statement?
Therefore, may I have helping hand from experts in lowyat forum?

Thank you everyone

This post has been edited by kinni: Nov 18 2015, 12:51 PM
Dothan
post Nov 18 2015, 12:56 PM

Dingle Berries
******
Senior Member
1,020 posts

Joined: Jan 2003


What database you are using? Oracle? MS SQL? MySQL?
TSkinni
post Nov 18 2015, 01:00 PM

Casual
***
Junior Member
320 posts

Joined: Nov 2009
From: Kajang/KL


QUOTE(Dothan @ Nov 18 2015, 12:56 PM)
What database you are using? Oracle? MS SQL? MySQL?
*
Microsoft SQL blush.gif
Dothan
post Nov 18 2015, 01:15 PM

Dingle Berries
******
Senior Member
1,020 posts

Joined: Jan 2003


QUOTE(kinni @ Nov 18 2015, 01:00 PM)
Microsoft SQL blush.gif
*
You may use replication function to do so but have to cast your numeric value as varchar (I assume it is numeric).

Example from my previous code:
CODE
select right(replicate('0',10) + cast(wafersize as varchar(10)), 10) from costfab


this will give me 0000000300 with 10 characters left padding max.

but I have to warn you, any datatype with numerical limit is 8000. You should use varchar(max) to try out.

Hope this help.
TSkinni
post Nov 18 2015, 01:38 PM

Casual
***
Junior Member
320 posts

Joined: Nov 2009
From: Kajang/KL


QUOTE(Dothan @ Nov 18 2015, 01:15 PM)
You may use replication function to do so but have to cast your numeric value as varchar (I assume it is numeric).

Example from my previous code:
CODE
select right(replicate('0',10) + cast(wafersize as varchar(10)), 10) from costfab


this will give me 0000000300 with 10 characters left padding max.

but I have to warn you, any datatype with numerical limit is 8000. You should use varchar(max) to try out.

Hope this help.
*
Thank you for helping me out from this, really appreciate it!!
notworthy.gif notworthy.gif notworthy.gif Salute. This is really fast than typing manually. Thank you so much
Dothan
post Nov 18 2015, 01:47 PM

Dingle Berries
******
Senior Member
1,020 posts

Joined: Jan 2003


QUOTE(kinni @ Nov 18 2015, 01:38 PM)
Thank you for helping me out from this, really appreciate it!!
notworthy.gif  notworthy.gif  notworthy.gif Salute. This is really fast than typing manually. Thank you so much
*
Glad it is working for you biggrin.gif
TSkinni
post Nov 18 2015, 01:51 PM

Casual
***
Junior Member
320 posts

Joined: Nov 2009
From: Kajang/KL


QUOTE(Dothan @ Nov 18 2015, 01:47 PM)
Glad it is working for you  biggrin.gif
*
May I ask, is it possible a column can store up to 9k length? LOL, I afraid it might not be work on 9k length.
TSkinni
post Nov 18 2015, 01:56 PM

Casual
***
Junior Member
320 posts

Joined: Nov 2009
From: Kajang/KL


QUOTE(Dothan @ Nov 18 2015, 01:47 PM)
Glad it is working for you  biggrin.gif
*
Seem like unable to reach 9k sweat.gif
Dothan
post Nov 18 2015, 01:58 PM

Dingle Berries
******
Senior Member
1,020 posts

Joined: Jan 2003


QUOTE(kinni @ Nov 18 2015, 01:56 PM)
Seem like unable to reach 9k  sweat.gif
*
Are you going to define a column with 9000 characters?

use varchar(max) instead of varchar(n)
TSkinni
post Nov 18 2015, 02:00 PM

Casual
***
Junior Member
320 posts

Joined: Nov 2009
From: Kajang/KL


QUOTE(Dothan @ Nov 18 2015, 01:58 PM)
Are you going to define a column with 9000 characters?

use varchar(max) instead of varchar(n)
*
Yes it is
I am doing in this way
right(replicate(' ',9999) + CAST(' ' as varchar(max)),9999) AS filler
Dothan
post Nov 18 2015, 02:02 PM

Dingle Berries
******
Senior Member
1,020 posts

Joined: Jan 2003


QUOTE(kinni @ Nov 18 2015, 02:00 PM)
Yes it is
I am doing in this way
right(replicate(' ',9999) + CAST(' ' as varchar(max)),9999) AS filler
*
I mean if you need to create table with a column that requires such long string, use max.
TSkinni
post Nov 18 2015, 02:11 PM

Casual
***
Junior Member
320 posts

Joined: Nov 2009
From: Kajang/KL


QUOTE(Dothan @ Nov 18 2015, 02:02 PM)
I mean if you need to create table with a column that requires such long string, use max.
*
rclxms.gif Understood. Thank you very much master rclxms.gif
aiskrimcup
post Nov 18 2015, 03:13 PM

Enthusiast
*****
Senior Member
750 posts

Joined: Jul 2009


rclxms.gif rclxms.gif rclxms.gif

 

Change to:
| Lo-Fi Version
0.0138sec    0.46    5 queries    GZIP Disabled
Time is now: 28th March 2024 - 09:11 PM