need to select a column with the repalcement of '0'

Question

need to select a column with the repalcement of '0'

deepu chan

Newbie

Points: 9
More actions
June 21, 2014 at 10:38 pm

#309320

i have a table like below
create table #temp
(
valid_id int not null,
valid_no varchar(50)
)
10001
20002
30011
40012
i need to select the valid no without '0' lie 1,2,11,12, how do i?
someone please clarify me 🙂

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

deepu chan (6/21/2014)
i have a table like below
create table #temp
(
valid_id int not null,
valid_no varchar(50)
)
10001
20002
30011
40012
i need to select the valid no without '0' lie 1,2,11,12, how do i?
someone please clarify me 🙂

You can use a type cast to a numeric data type.

😎

USE tempdb;

GO

;WITH TVAL (valid_id,valid_no) AS

(SELECT valid_id,valid_no FROM

(VALUES

(1,'0001')

,(2,'0002')

,(3,'0011')

,(4,'0012')

) AS X(valid_id,valid_no)

)

SELECT

T.valid_id

,CAST(T.valid_no AS INT) AS valid_no

FROM TVAL T

Results

valid_id valid_no

----------- -----------

1 1

2 2

3 11

4 12

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

What should happen in case of trailing zero's ? Example 1000,9990 or zero's that are neither leading or trailing ex : 1001

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

Sachin Nandanwar (6/21/2014)
What should happen in case of trailing zero's ? Example 1000,9990 or zero's that are neither leading or trailing ex : 1001

Those would then certainly be valid numbers.

😎

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 4

Eirikur Eiriksson (6/21/2014)
Sachin Nandanwar (6/21/2014)
What should happen in case of trailing zero's ? Example 1000,9990 or zero's that are neither leading or trailing ex : 1001
Those would then certainly be valid numbers.
😎

Makes sense.But the way OP framed his/her question I thought he/she wants to replace all the '0' in a given number.

i need to select the valid no without '0' lie 1,2,11,12, how do i?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 5

Sachin Nandanwar (6/22/2014)
Eirikur Eiriksson (6/21/2014)
Sachin Nandanwar (6/21/2014)
What should happen in case of trailing zero's ? Example 1000,9990 or zero's that are neither leading or trailing ex : 1001
Those would then certainly be valid numbers.
😎
Makes sense.But the way OP framed his/her question I thought he/she wants to replace all the '0' in a given number.
i need to select the valid no without '0' lie 1,2,11,12, how do i?

It's the usual "leading zeros" problem:-D

😎

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

Eirikur Eiriksson (6/22/2014)
Sachin Nandanwar (6/22/2014)
Eirikur Eiriksson (6/21/2014)
Sachin Nandanwar (6/21/2014)
What should happen in case of trailing zero's ? Example 1000,9990 or zero's that are neither leading or trailing ex : 1001
Those would then certainly be valid numbers.
😎
Makes sense.But the way OP framed his/her question I thought he/she wants to replace all the '0' in a given number.
i need to select the valid no without '0' lie 1,2,11,12, how do i?
It's the usual "leading zeros" problem:-D
😎

This stems from using the improper datatypes.

OP, your life would be a LOT easier if you used a numeric datatype to hold numeric data instead of varchar. You are struggling with this because you should have used numeric as the datatype which would have eliminated the issue here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101200 More actions · Answer 7

I'd strongly urge you to use a character-based method rather than simply trying to CAST to numeric, which is almost certain to cause a non-numeric data exception.

SELECT valid_id, SUBSTRING(valid_no, PATINDEX('%[^0]%', valid_no + '.'), 10)

FROM #temp

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".