CODEWITHSUNDEEP
sqlsql-server
user1401498
user1401498

Reputation:

Truncating leading zeros in sql server

I need to represent the following records 

DATA
000200AA
00000200AA
000020BCD
00000020BCD
000020ABC

AS

DATA    CNT
200AA   1
20BCD   2
20ABC   2

ANY IDEAS?

Upvotes: 2

Views: 1079

Answers (6)

Lamak
Lamak

Reputation: 70638

You can do this query:

SELECT RIGHT([DATA],LEN[DATA])-PATINDEX('%[1-9]%',[DATA])+1) [DATA], COUNT(*) CNT
FROM YourTable
GROUP BY RIGHT([DATA],LEN[DATA])-PATINDEX('%[1-9]%',[DATA])+1)

Upvotes: 0

Diego
Diego

Reputation: 36126

take a look at the STUFF function

It inserts data into a string on a range

Upvotes: 0

Charleh
Charleh

Reputation: 14012

Depending on the what you need to get the values this code may differ:

Assuming a simple right 5 chars as Barry suggested, you can use RIGHT(data, 5) and GROUP BY and COUNT to get your results

http://sqlfiddle.com/#!3/19ecd/2

Upvotes: 1

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26727

DECLARE @String VARCHAR(32) = N'000200AA'

SELECT  SUBSTRING ( @String ,CHARINDEX(N'2', @String),LEN(@String))

Upvotes: 1

sumit
sumit

Reputation: 15464

USE patindex

select count(test) as cnt,
 substring(test, patindex('%[^0]%',test),len(test)) from (

  select ('000200AA') as test
  union
  select '00000200AA' as test
  union
  select ('000020BCD') as test
  union
  select ('00000020BCD') as test
  union
  select ('000020ABC') as test

  )ty
 group by substring(test, patindex('%[^0]%',test),len(test))

Upvotes: 8

David M
David M

Reputation: 72850

How about a nice recursive user-defined function?

CREATE FUNCTION dbo.StripLeadingZeros (
        @input varchar(MAX)
) RETURNS varchar(MAX)
BEGIN

    IF LEN(@input) = 0
        RETURN @input

    IF SUBSTRING(@input, 1, 1) = '0'
        RETURN dbo.StripLeadingZeros(SUBSTRING(@input, 2, LEN(@input) - 1))

    RETURN @input

END
GO

Then:

SELECT dbo.StripLeadingZeros(DATA) DATA, COUNT(DATA) CNT
FROM YourTable GROUP BY dbo.StripLeadingZeros(DATA)

Upvotes: 3

Related Questions