Sql Server - Replace only the first characters in a string

Question

How can I replace only the the first zeros in a string? Example:

string : '0000089001232100'
result:  '89001232100'

I can't use SQL Server REPLACE function since I don't want all my zeros to be replaced. Thanks

What about just converting the string to `int` or `bigint`? – Jeff Rosenberg Mar 04 '13 at 14:14 — Jeff Rosenberg, Mar 04 '13 at 14:14

score 3 · Accepted Answer · answered Mar 04 '13 at 14:17

select substring(ColumnName, patindex('%[^0]%',ColumnName), 10) should give you what you need.

Also look into the RIGHT and LEFT functions. They strip a string from the left or right side, respectively.

If you wanted to use RIGHT, you could get the index of the first 'wanted' character using CHARINDEX(http://msdn.microsoft.com/en-us/library/ms186323.aspx), then use that index as the point to strip from in your RIGHT function.

score 2 · Answer 2 · answered Mar 04 '13 at 14:16

2

If the string always have only numbers, you could use this:

DECLARE @String VARCHAR(30)
SET @String = '0000089001232100'
SELECT CONVERT(VARCHAR(20),CONVERT(NUMERIC(20,0),@String))

answered Mar 04 '13 at 14:16

Lamak

69,480
12
108
116

Sql Server - Replace only the first characters in a string

2 Answers2