Get Top 5 characters only - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Get Top 5 characters only

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-08-03 : 04:19:40

I have a column brnZipCode which have varchar(9) as possible length.
in select statement I have to populate it in sucha way that if it's length is greater then 5 then take only top 5 characters other wise full brnZipCode .

My current query is in such a way

select DerivedEntryPoint = CASE
WHEN BP.brnZipCode IS NOT NULL THEN BP.brnZipCode
ELSE csm.csmContainerDestinationZip
END
From csm,BP ..........

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-03 : 04:24:38

[code]case when len(brnZipCode)> 5 then left(brnZipCode, 5) else brnZipCode end[/code]

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-08-03 : 04:42:36

it required two case statement i think.
(as if i have to put other fileds data if the brnZipciode is null)
can't it be more simplified?

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-08-03 : 05:39:08

[code]
isnull(case when len(brnZipCode)> 5 then left(brnZipCode, 5) else csmContainerDestinationZip end, csmContainerDestinationZip)[/code]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 06:48:30

You can use LEFT function without regard to how long the string is. If the string happens to be shorter than the length parameter of the LEFT function, it will simply return the entire string.

DECLARE @x VARCHAR(9);

SET @x = 'ABCDEDGHK';
SELECT LEFT(@x,5);
--- returns 'ABCDE'

SET @x = 'AB';
SELECT LEFT(@x,5);
--- returns 'AB'

If your zip happens to be null and you want to handle that, you can use the COALESCE on top of that. For example,
SELECT COALESCE(LEFT(@x,5),' None ');

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-08-06 : 02:11:57

Thanks Sunit

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources