Want to combine last_name + first_name= company

Question

1.00/5 (1 vote)

See more:

I'm getting some of the records good e.g

Last_Name:- COOKIES FASHION BOUT
First_Name:- IQUE
Company:- COOKIES FASHION BOUTIQUE

-----------------------------------
but in other case

Last_Name:- COOL TEXTILE IMPORT
First_Name:- LTD
Company :- COOL TEXTILE IMPORTLTD

But I need the space between
COOL TEXTILE IMPORT LTD there

Need to fix these records..

What I have tried:

SQL

Select *, concat(Ltrim(Rtrim(Last_Name)),Ltrim(Rtrim(First_Name))) as Company from tbl
Go
--

Posted 17-Dec-18 4:24am

Member 13138564

Updated 18-Dec-18 22:56pm

Patrice T

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Answer 1 · 2018-12-17T04:29:00

Solution 1

Select *, Ltrim(Rtrim(Last_Name)) + ' ' + Ltrim(Rtrim(First_Name)) as Company from tbl

However if you're showing this information somewhere else like a form or a website then I'd select the two columns as separate fields and do the concatenation in the display layer.

Posted 17-Dec-18 4:29am

F-ES Sitecore

OriginalGriff · Answer 2 · 2018-12-17T04:42:00

You are either looking at changing your data - adding a special characters that you later replace with a space in front of the LTD perhaps - or adding "special cases" where you look for specific endings like LTD, CO, PLC, and so on and specifically leave the spaces there.

Personally, I'd start by looking at where I got the data from, and see if it can be fixed there instead of later when it's used ... it's always messy to fix up data automatically once it's in your DB.

Patrice T · Answer 3 · 2018-12-17T05:37:00

Solution 3

There is no solution to your problem because of the way data was stored in first place.
Sometimes you need to add a space, sometimes you don't, there is no way to know when.

The only solution is to not remove spaces when data is stored.

Posted 17-Dec-18 5:37am

Patrice T

PRAKASH9 · Answer 4 · 2018-12-18T22:57:00

Solution 4

You just have to know where you want spaces between lastname and first name then those records you have to update in database only
for example : you have to add one more space in First_Name record in db

Posted 18-Dec-18 22:57pm

PRAKASH9