Left-justifying substring-Easy, ya'd think

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left-justifying substring-Easy, ya'd think

Author

Topic

scared
Starting Member

14 Posts

Posted - 2007-06-09 : 15:59:01

Having a whale of a time, having tried almost every approach, except obviously the right one.

I have employee_no which is character size 10. It is right-justified for some reason. I'm using SUBSTRING to peel off the last five digits (it's going to a flat file where only 5 chars are allowed).

Now I just need to Left Justify the return string. What should I add
to this statement

substring(employees.employee_no,6,8)

My return is this:

A97 1Joe
A97 3Bruce
A97 4Scott

Many thanks in advance, you guys are amazing.
lisa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-09 : 19:26:33

You want to pad it to 5 chars ?

left(substring(employees.employee_no,6,8) + '     ', 5)

scared
Starting Member

14 Posts

Posted - 2007-06-11 : 09:35:50

quote:
Originally posted by khtan

You want to pad it to 5 chars ?
left(substring(employees.employee_no,6,8) + ' ', 5)

KH

Khtan - that didn't left justify it.

A97 1Joe
A97 3Bruce
A97 4Scott

The commands I'm using before I pasted your statement are

+ COALESCE(CONVERT(char(1),' '),'')
+ left(substring(employees.employee_no,6,8) + ' ', 5)
+ COALESCE(CONVERT(char(10),first_name),'')

lisa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 09:39:04

You need to be little more specific about what you mean by "Left Justify"? Please give sample output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

scared
Starting Member

14 Posts

Posted - 2007-06-11 : 09:50:23

quote:
Originally posted by harsh_athalye

You need to be little more specific about what you mean by "Left Justify"? Please give sample output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Return is this:
A97 1Joe
A97 3Bruce
A97 4Scott

Trying to get it to look like this:
A971 Joe
A973 Bruce
A974 Scott

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 10:53:52

Try

Replace(left(col,5),' ','')+' '+substring(col,6,8)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources