Solved

Concaenate field that needs to be 30 characters long

Posted on 2014-11-26
6
98 Views
Last Modified: 2014-11-26
Hi there,
I have the following statement:
select 
REPLACE(concat(glje_ref1,glje_ref2, glje_ref3, glje_ref4), ' ', '')
from mytable

Open in new window

and this returns a string with different lengths. What I need to do is to return the first 30 Characters it is ok if the trailing character are cut of
what's the best practice on going about this?
Thanks
0
Comment
Question by:COHFL
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 40467268
select LEFT (REPLACE(concat(glje_ref1,glje_ref2, glje_ref3, glje_ref4), ' ', '') , 30 )
0
 

Author Comment

by:COHFL
ID: 40467270
Im thinking on
SUBSTRING(REPLACE(concat(glje_ref1,glje_ref2, glje_ref3, glje_ref4), ' ', '')
, 1, 30)

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40467290
Either of these will work, as they both return a 30-character varchar:
select CAST(REPLACE(concat(glje_ref1,glje_ref2, glje_ref3, glje_ref4), ' ', '') as varchar(30)) 
from mytable

select LEFT(REPLACE(concat(glje_ref1,glje_ref2, glje_ref3, glje_ref4), ' ', ''), 30) 
from mytable

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:COHFL
ID: 40467304
they all worked but one thing I guess I did not account it for is when there is not data under any of the refs or the string is less than 30 the replace kick in and remove empty spaces. and if the string is less said 20 characters I need it to be 30 characters long how do I achieve this?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 40467307
select LEFT (REPLACE(concat(glje_ref1,glje_ref2, glje_ref3, glje_ref4), ' ', '')+replicate('-',30 )  , 30 )
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40467332
>I need it to be 30 characters long
In that case CAST it as a CHAR instead of VARCHAR.  
To demonstrate copy-paste the below code into your SSMS and execute.
Declare @val varchar(50) = '12345678901234567890'

SELECT DATALENGTH(CAST(@val as char(30)))
SELECT DATALENGTH(CAST(@val as varchar(30)))
SELECT DATALENGTH(LEFT(@val, 30))

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now