Solved

Concaenate field that needs to be 30 characters long

Posted on 2014-11-26
6
102 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

803 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