Solved

Concaenate field that needs to be 30 characters long

Posted on 2014-11-26
6
110 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 66

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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 66

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

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.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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