Remove Zero in the midst - 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 2005 Forums

Transact-SQL (2005)

Remove Zero in the midst

Author

Topic

andriancruz
Starting Member

38 Posts

Posted - 2008-05-11 : 23:52:49

Hi Everyone,

Can you help me on my problem, I have a data 'ABC0000000000000027', i want to change it. i want to will become 'ABC27'. what I want is remove all the '0'. how to write the query to remove the '0'?

thanks in advance

andriancruz

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-12 : 00:00:25

Take look at replace function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 00:13:42

SELECT REPLACE('ABC0000000000000027','0','')

andriancruz
Starting Member

38 Posts

Posted - 2008-05-12 : 00:43:48

Thank you visakh16. its working. but my problem now is even the 'ABC0000000000000010' will become 'ABC1' supposed to be 'ABC10'

andriancruz

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-12 : 03:23:17

hi,
pls try this........

SELECT SUBSTRING('ABC0000000000000010',1,3) +''+CAST((0+ SUBSTRING('ABC0000000000000010',4,LEN('ABC0000000000000010'))) AS VARCHAR(5))

ok tanx.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-12 : 03:30:45

[code]DECLARE @Sample TABLE (Info VARCHAR(50))

INSERT @Sample
SELECT 'ABC0000000000000027' UNION ALL
SELECT 'SQLTEAM000000040040' UNION ALL
SELECT 'ABC0000000000000010'

SELECT LEFT(Info, Pos - 1) + CAST(CAST(SUBSTRING(Info, Pos, 8000) AS BIGINT) AS VARCHAR(8000))
FROM (
SELECT PATINDEX('%[0-9]%', Info) AS Pos,
Info
FROM @Sample
) AS d[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-12 : 05:21:26

From the power of Implicit convertions


SELECT	LEFT(Info, Pos - 1) + RTRIM(SUBSTRING(Info, Pos, 8000)*1)
FROM	(
		SELECT	PATINDEX('%[0-9]%', Info) AS Pos,
			Info
		FROM	@Sample
	) AS d

Madhivanan

Failing to plan is Planning to fail

andriancruz
Starting Member

38 Posts

Posted - 2008-05-12 : 05:53:39

hi soorajtnpki, thank you so much for your reply. its working now. more power to you.

andrian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-12 : 05:56:50

quote:
Originally posted by andriancruz

hi soorajtnpki, thank you so much for your reply. its working now. more power to you.

andrian

Peso's and mine are more generalised
Try them as well

Madhivanan

Failing to plan is Planning to fail

pdreyer
Starting Member

6 Posts

Posted - 2008-05-12 : 06:27:09

Another alternative

select stuff(x,c,p-c,'') 
from 
(select charindex('0',x) 
 ,patindex('%[1-9]%',x) 
 ,x
 from 
 (select 'ABC0000000000000027' union all
  select 'SQLTEAM000000040040' union all
  select 'ABC0000000000000010' union all
  select 'ABC000000000000021A'
 )s(x)
)t(c,p,x)

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-12 : 08:44:17

My try on it.

SELECT Info,LEFT(T.INFO,Pos) + ' ' + SUBSTRING(info,PATINDEX('%[1-2-3-4-5-6-7-8-9]%',Info),LEN(info))AS 'Output'
From
( SELECT PATINDEX('%[0-9]%',Info)-1 as pos,Info FROM @Sample
) T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources