August 22, 2012 at 10:57 am
hi ,
I have two strings like ex:1.'000temp1'
2.'0000temp2'..like
how can i remove the zeros from my string..
August 22, 2012 at 11:36 am
REPLACE( string, '0', '')
Use the help or Books OnLine for SQL Server.
August 23, 2012 at 4:44 am
dastagiri16 (8/22/2012)
hi ,I have two strings like ex:1.'000temp1'
2.'0000temp2'..like
how can i remove the zeros from my string..
You didn't specify, other than by the eaxmple, but this will just eliminate leading zeros.
select REPLACE(LTRIM(REPLACE('000temp001', '0', ' ')), ' ', '0')
August 24, 2012 at 9:16 am
Try this:
CREATE FUNCTION dbo.svfRemoveLeftMostCharacters
(
@Source VARCHAR(1000)
,@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
/* This function requires a Tally or Numbers table */
/* http://www.sqlservercentral.com/articles/T-SQL/62867/ */
DECLARE
@NewValue VARCHAR(1000)
,@Pos INT
SET @Source = LTRIM(@Source)
;WITH Numbers
AS (
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
dbo.Tally
WHERE
N <= LEN(@Source)
)
SELECT TOP(1)
@Pos = t.N
FROM
dbo.Tally AS t
WHERE
SUBSTRING(@Source,t.N,1) <> @Char
AND N <= LEN(@Source)
ORDER BY t.N
OPTION
(MAXRECURSION 0)
SET @NewValue = SUBSTRING(@Source,@Pos,LEN(@Source))
RETURN @NewValue
/*
SELECT dbo.svfRemoveLeftMostCharacters('0001234056000','0')
SELECT dbo.svfRemoveLeftMostCharacters('XYZ1234056000','X')
SELECT dbo.svfRemoveLeftMostCharacters('99999999991234056000','9')
*/
END
August 24, 2012 at 9:46 am
Steven Willis (8/24/2012)
Try this:
CREATE FUNCTION dbo.svfRemoveLeftMostCharacters
(
@Source VARCHAR(1000)
,@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
/* This function requires a Tally or Numbers table */
/* http://www.sqlservercentral.com/articles/T-SQL/62867/ */
DECLARE
@NewValue VARCHAR(1000)
,@Pos INT
SET @Source = LTRIM(@Source)
;WITH Numbers
AS (
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
dbo.Tally
WHERE
N <= LEN(@Source)
)
SELECT TOP(1)
@Pos = t.N
FROM
dbo.Tally AS t
WHERE
SUBSTRING(@Source,t.N,1) <> @Char
AND N <= LEN(@Source)
ORDER BY t.N
OPTION
(MAXRECURSION 0)
SET @NewValue = SUBSTRING(@Source,@Pos,LEN(@Source))
RETURN @NewValue
/*
SELECT dbo.svfRemoveLeftMostCharacters('0001234056000','0')
SELECT dbo.svfRemoveLeftMostCharacters('XYZ1234056000','X')
SELECT dbo.svfRemoveLeftMostCharacters('99999999991234056000','9')
*/
END
Please don't! Until you want to implement one of the slowest way of achieving it.
To remove "LeftMostCharacters" as per all above can be done by simple:
SELECT SUBSTRING(@Source,PATINDEX('%[^'+@Char+']%',@Source),1000)
*removed X from the pattern
August 24, 2012 at 10:00 am
tyson.price (8/23/2012)
dastagiri16 (8/22/2012)
hi ,I have two strings like ex:1.'000temp1'
2.'0000temp2'..like
how can i remove the zeros from my string..
You didn't specify, other than by the eaxmple, but this will just eliminate leading zeros.
select REPLACE(LTRIM(REPLACE('000temp001', '0', ' ')), ' ', '0')
I had never thought of approaching it that way before...Thank you for the suggestion.
August 24, 2012 at 10:43 am
Eugene Elutin (8/24/2012)
Steven Willis (8/24/2012)
Try this:
CREATE FUNCTION dbo.svfRemoveLeftMostCharacters
(
@Source VARCHAR(1000)
,@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
/* This function requires a Tally or Numbers table */
/* http://www.sqlservercentral.com/articles/T-SQL/62867/ */
DECLARE
@NewValue VARCHAR(1000)
,@Pos INT
SET @Source = LTRIM(@Source)
;WITH Numbers
AS (
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
dbo.Tally
WHERE
N <= LEN(@Source)
)
SELECT TOP(1)
@Pos = t.N
FROM
dbo.Tally AS t
WHERE
SUBSTRING(@Source,t.N,1) <> @Char
AND N <= LEN(@Source)
ORDER BY t.N
OPTION
(MAXRECURSION 0)
SET @NewValue = SUBSTRING(@Source,@Pos,LEN(@Source))
RETURN @NewValue
/*
SELECT dbo.svfRemoveLeftMostCharacters('0001234056000','0')
SELECT dbo.svfRemoveLeftMostCharacters('XYZ1234056000','X')
SELECT dbo.svfRemoveLeftMostCharacters('99999999991234056000','9')
*/
END
Please don't! Until you want to implement one of the slowest way of achieving it.
To remove "LeftMostCharacters" as per all above can be done by simple:
SELECT SUBSTRING(@Source,PATINDEX('%[^'+@Char+'X]%',@Source),1000)
I guess both approaches work but it touches on one of my pet peeves. I believe in the KISS programming approach (Keep It Simple Stupid). Way too many times have I seen elegant yet esoteric code in place from programmers stretching their programming legs and being creative. That all well and fine if you are supporting your own code but in a business environment where you have varying levels of expertise I say "keep it simple".
Most programmers would need to research what "'%[^'+@Char+'X]%'" means if they came across it supporting code. Why put that in place when something much more readable can be used?
I have seen deeply nested if statements that could have been linier with exit sub logic. Multi dimensional tables with nested looping iterations the Einstein would squint at. Code chopped out of interesting web sites that you would need to research to support.
I guess there is a balance somewhere but I've been in the situation where I have to take time myself or help junior programmers debug code that a straight simple approach would have avoided.
I mean absolutely nothing disparaging against the folks that posted the code and they are both workable solutions. It’s good to see different ways of accomplishing the same thing. They just reminded me of my pet peeve and I'm venting. Maybe it’s just too many years of seeing code that could launch a rocket in a simple accounting system.
August 24, 2012 at 11:28 am
tyson.price (8/24/2012)
Most programmers would need to research what "'%[^'+@Char+'X]%'" means if they came across it supporting code. Why put that in place when something much more readable can be used?
I understand your point, there's no need to use unreadable code if there's a simpler way to do it. However, that used to be the rule on the company I used to work for and everyone used cursors for everything :sick:. The performance wasn't great to say it nicely. As you said, there should be a balance.
If most programmers need to research what "'%[^'+@Char+'X]%'" means, wouldn't it be a good way of learning something? It's not hard to find out what's doing, it will take a few minutes and they will learn something new.
As a matter of fact, both solutions have to be corrected to avoid problems. One is creating a virtual tally table with tally table using a recursive CTE instead of using dbo.tally directly and the second one doesn't need the X and should be removed as it can generate problems.
August 24, 2012 at 11:35 am
Luis Cazares (8/24/2012)
tyson.price (8/24/2012)
Most programmers would need to research what "'%[^'+@Char+'X]%'" means if they came across it supporting code. Why put that in place when something much more readable can be used?
I understand your point, there's no need to use unreadable code if there's a simpler way to do it. However, that used to be the rule on the company I used to work for and everyone used cursors for everything :sick:. The performance wasn't great to say it nicely. As you said, there should be a balance.
If most programmers need to research what "'%[^'+@Char+'X]%'" means, wouldn't it be a good way of learning something? It's not hard to find out what's doing, it will take a few minutes and they will learn something new.
As a matter of fact, both solutions have to be corrected to avoid problems. One is creating a virtual tally table with tally table using a recursive CTE instead of using dbo.tally directly and the second one doesn't need the X and should be removed as it can generate problems.
No arguments and when I thought more about my post I realized it may have been better to make it a standalone post versus attaching it to someone else's thoughts/methods. That way no one would take it personal.
Shop standards can also force your hand as you mentioned.
Thanks!
August 24, 2012 at 11:46 am
tyson.price (8/24/2012)
Eugene Elutin (8/24/2012)
Steven Willis (8/24/2012)
Try this:
CREATE FUNCTION dbo.svfRemoveLeftMostCharacters
(
@Source VARCHAR(1000)
,@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
/* This function requires a Tally or Numbers table */
/* http://www.sqlservercentral.com/articles/T-SQL/62867/ */
DECLARE
@NewValue VARCHAR(1000)
,@Pos INT
SET @Source = LTRIM(@Source)
;WITH Numbers
AS (
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
dbo.Tally
WHERE
N <= LEN(@Source)
)
SELECT TOP(1)
@Pos = t.N
FROM
dbo.Tally AS t
WHERE
SUBSTRING(@Source,t.N,1) <> @Char
AND N <= LEN(@Source)
ORDER BY t.N
OPTION
(MAXRECURSION 0)
SET @NewValue = SUBSTRING(@Source,@Pos,LEN(@Source))
RETURN @NewValue
/*
SELECT dbo.svfRemoveLeftMostCharacters('0001234056000','0')
SELECT dbo.svfRemoveLeftMostCharacters('XYZ1234056000','X')
SELECT dbo.svfRemoveLeftMostCharacters('99999999991234056000','9')
*/
END
Please don't! Until you want to implement one of the slowest way of achieving it.
To remove "LeftMostCharacters" as per all above can be done by simple:
SELECT SUBSTRING(@Source,PATINDEX('%[^'+@Char+'X]%',@Source),1000)
I guess both approaches work but it touches on one of my pet peeves. I believe in the KISS programming approach (Keep It Simple Stupid). Way too many times have I seen elegant yet esoteric code in place from programmers stretching their programming legs and being creative. That all well and fine if you are supporting your own code but in a business environment where you have varying levels of expertise I say "keep it simple".
Most programmers would need to research what "'%[^'+@Char+'X]%'" means if they came across it supporting code. Why put that in place when something much more readable can be used?
I have seen deeply nested if statements that could have been linier with exit sub logic. Multi dimensional tables with nested looping iterations the Einstein would squint at. Code chopped out of interesting web sites that you would need to research to support.
I guess there is a balance somewhere but I've been in the situation where I have to take time myself or help junior programmers debug code that a straight simple approach would have avoided.
I mean absolutely nothing disparaging against the folks that posted the code and they are both workable solutions. It’s good to see different ways of accomplishing the same thing. They just reminded me of my pet peeve and I'm venting. Maybe it’s just too many years of seeing code that could launch a rocket in a simple accounting system.
If you don't have a dbo.Tally (or dbo.Numbers) table this is how I would replace the dynamic numbers table in SQL Server 2005:
with
e1(n) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1), -- 10 rows
e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows
e4(n) as (select 1 from e2 a cross join e2 b), -- 10,000 rows
Numbers(n) as (select row_number() over (order by (select null)) n from e4 a cross join e2 b) -- 1,000,000 rows
From there it depends on what you need to do. You can use a TOP to restrict the number of rows actually generated from the Numbers cte.
August 24, 2012 at 12:05 pm
tyson.price (8/24/2012)
Eugene Elutin (8/24/2012)
Steven Willis (8/24/2012)
Try this:
CREATE FUNCTION dbo.svfRemoveLeftMostCharacters
(
@Source VARCHAR(1000)
,@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
/* This function requires a Tally or Numbers table */
/* http://www.sqlservercentral.com/articles/T-SQL/62867/ */
DECLARE
@NewValue VARCHAR(1000)
,@Pos INT
SET @Source = LTRIM(@Source)
;WITH Numbers
AS (
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
dbo.Tally
WHERE
N <= LEN(@Source)
)
SELECT TOP(1)
@Pos = t.N
FROM
dbo.Tally AS t
WHERE
SUBSTRING(@Source,t.N,1) <> @Char
AND N <= LEN(@Source)
ORDER BY t.N
OPTION
(MAXRECURSION 0)
SET @NewValue = SUBSTRING(@Source,@Pos,LEN(@Source))
RETURN @NewValue
/*
SELECT dbo.svfRemoveLeftMostCharacters('0001234056000','0')
SELECT dbo.svfRemoveLeftMostCharacters('XYZ1234056000','X')
SELECT dbo.svfRemoveLeftMostCharacters('99999999991234056000','9')
*/
END
Please don't! Until you want to implement one of the slowest way of achieving it.
To remove "LeftMostCharacters" as per all above can be done by simple:
SELECT SUBSTRING(@Source,PATINDEX('%[^'+@Char+'X]%',@Source),1000)
I guess both approaches work but it touches on one of my pet peeves. I believe in the KISS programming approach (Keep It Simple Stupid). Way too many times have I seen elegant yet esoteric code in place from programmers stretching their programming legs and being creative. That all well and fine if you are supporting your own code but in a business environment where you have varying levels of expertise I say "keep it simple".
Most programmers would need to research what "'%[^'+@Char+'X]%'" means if they came across it supporting code. Why put that in place when something much more readable can be used?
I have seen deeply nested if statements that could have been linier with exit sub logic. Multi dimensional tables with nested looping iterations the Einstein would squint at. Code chopped out of interesting web sites that you would need to research to support.
I guess there is a balance somewhere but I've been in the situation where I have to take time myself or help junior programmers debug code that a straight simple approach would have avoided.
I mean absolutely nothing disparaging against the folks that posted the code and they are both workable solutions. It’s good to see different ways of accomplishing the same thing. They just reminded me of my pet peeve and I'm venting. Maybe it’s just too many years of seeing code that could launch a rocket in a simple accounting system.
I absolutely agree with a modifier... "Keep it simple... unless it can cause performance problems in the face of scalability" and this code really will cause performance problems. The given function uses a very slow and resource intensive recursive "counting" CTE (which could also be called "esoteric" and not simple by some developer's standards) as well as being a Scalar UDF which is usually 7 times slower than most other methods except a cursor or While Loop.
Never use a small row count as justification for such simplicity either... if a function is available, someone somewhere will use it in a place that requires much more scalable code.
For something that is thought to be or really is "esoteric", it takes just a second to explain it in comments.
Please see the following articles for code proof on the pitfalls of Scalar UDFs and recursive CTEs that count.
http://www.sqlservercentral.com/articles/T-SQL/91724/
http://www.sqlservercentral.com/articles/T-SQL/74118/
Also, please don't read into the Recursive CTE article. I'm not saying that all recursive CTEs are necessarily bad. I'm only speaking of recursive CTE's that count.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2012 at 12:08 pm
...
As a matter of fact, both solutions have to be corrected to avoid problems. One is creating a virtual tally table with tally table using a recursive CTE instead of using dbo.tally directly and the second one doesn't need the X and should be removed as it can generate problems.
I hope, you can guess that X was leftover from just hard-coded version..., thank you for spotting - I will edit the original post.
Actually, there is no reason to wrap this thing into UDF, in-line code would perform just perfect:
SELECT SUBSTRING([ColumnFromTableOrVariable],PATINDEX('%[^X]%',@Source),1000)
Please Note:
1. "X" is the character to remove. Can be simply replaced with almost any other one. Why almost? Because for some ASCII characters cannot be used in "a simple" way - they should be escaped.
2. 1000 - is the length I've took from the top of my head, you better to use the real length of the applicable column/variable.
You can see that this technique, can be used for example to remove any leading digits as well. For example if you want to remove leading digits from "1972GoodYear1973", you can use '%[^0-9]%' expression.
I think that learning patters/regular expressions, will most likely add quite a valuable skill to any IT processional.
For the example with use of tally table. Of course the worst thing there was use of recursive CTE.
But even without it I cannot see point of using the tally based solution for a given task.
And knowing me, for any performance sensitive things, I would use CLR for most of string formatting operations.
August 24, 2012 at 12:39 pm
tyson.price (8/24/2012)
Eugene Elutin (8/24/2012)
Steven Willis (8/24/2012)
Try this:
CREATE FUNCTION dbo.svfRemoveLeftMostCharacters
(
@Source VARCHAR(1000)
,@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
/* This function requires a Tally or Numbers table */
/* http://www.sqlservercentral.com/articles/T-SQL/62867/ */
DECLARE
@NewValue VARCHAR(1000)
,@Pos INT
SET @Source = LTRIM(@Source)
;WITH Numbers
AS (
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
dbo.Tally
WHERE
N <= LEN(@Source)
)
SELECT TOP(1)
@Pos = t.N
FROM
dbo.Tally AS t
WHERE
SUBSTRING(@Source,t.N,1) <> @Char
AND N <= LEN(@Source)
ORDER BY t.N
OPTION
(MAXRECURSION 0)
SET @NewValue = SUBSTRING(@Source,@Pos,LEN(@Source))
RETURN @NewValue
/*
SELECT dbo.svfRemoveLeftMostCharacters('0001234056000','0')
SELECT dbo.svfRemoveLeftMostCharacters('XYZ1234056000','X')
SELECT dbo.svfRemoveLeftMostCharacters('99999999991234056000','9')
*/
END
Please don't! Until you want to implement one of the slowest way of achieving it.
To remove "LeftMostCharacters" as per all above can be done by simple:
SELECT SUBSTRING(@Source,PATINDEX('%[^'+@Char+'X]%',@Source),1000)
I guess both approaches work but it touches on one of my pet peeves. I believe in the KISS programming approach (Keep It Simple Stupid). Way too many times have I seen elegant yet esoteric code in place from programmers stretching their programming legs and being creative. That all well and fine if you are supporting your own code but in a business environment where you have varying levels of expertise I say "keep it simple".
Most programmers would need to research what "'%[^'+@Char+'X]%'" means if they came across it supporting code. Why put that in place when something much more readable can be used?
I have seen deeply nested if statements that could have been linier with exit sub logic. Multi dimensional tables with nested looping iterations the Einstein would squint at. Code chopped out of interesting web sites that you would need to research to support.
I guess there is a balance somewhere but I've been in the situation where I have to take time myself or help junior programmers debug code that a straight simple approach would have avoided.
I mean absolutely nothing disparaging against the folks that posted the code and they are both workable solutions. It’s good to see different ways of accomplishing the same thing. They just reminded me of my pet peeve and I'm venting. Maybe it’s just too many years of seeing code that could launch a rocket in a simple accounting system.
I get your point but, that is why we have comments. I will take performance over easy to read any day of the week. Comments are easy to type and can make some incredibly complicated code easy to understand.
_______________________________________________________________
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/
August 24, 2012 at 12:44 pm
Sean Lange (8/24/2012)
tyson.price (8/24/2012)
Eugene Elutin (8/24/2012)
Steven Willis (8/24/2012)
Try this:
CREATE FUNCTION dbo.svfRemoveLeftMostCharacters
(
@Source VARCHAR(1000)
,@Char CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
/* This function requires a Tally or Numbers table */
/* http://www.sqlservercentral.com/articles/T-SQL/62867/ */
DECLARE
@NewValue VARCHAR(1000)
,@Pos INT
SET @Source = LTRIM(@Source)
;WITH Numbers
AS (
SELECT
1 AS N
UNION ALL
SELECT
N + 1
FROM
dbo.Tally
WHERE
N <= LEN(@Source)
)
SELECT TOP(1)
@Pos = t.N
FROM
dbo.Tally AS t
WHERE
SUBSTRING(@Source,t.N,1) <> @Char
AND N <= LEN(@Source)
ORDER BY t.N
OPTION
(MAXRECURSION 0)
SET @NewValue = SUBSTRING(@Source,@Pos,LEN(@Source))
RETURN @NewValue
/*
SELECT dbo.svfRemoveLeftMostCharacters('0001234056000','0')
SELECT dbo.svfRemoveLeftMostCharacters('XYZ1234056000','X')
SELECT dbo.svfRemoveLeftMostCharacters('99999999991234056000','9')
*/
END
Please don't! Until you want to implement one of the slowest way of achieving it.
To remove "LeftMostCharacters" as per all above can be done by simple:
SELECT SUBSTRING(@Source,PATINDEX('%[^'+@Char+'X]%',@Source),1000)
I guess both approaches work but it touches on one of my pet peeves. I believe in the KISS programming approach (Keep It Simple Stupid). Way too many times have I seen elegant yet esoteric code in place from programmers stretching their programming legs and being creative. That all well and fine if you are supporting your own code but in a business environment where you have varying levels of expertise I say "keep it simple".
Most programmers would need to research what "'%[^'+@Char+'X]%'" means if they came across it supporting code. Why put that in place when something much more readable can be used?
I have seen deeply nested if statements that could have been linier with exit sub logic. Multi dimensional tables with nested looping iterations the Einstein would squint at. Code chopped out of interesting web sites that you would need to research to support.
I guess there is a balance somewhere but I've been in the situation where I have to take time myself or help junior programmers debug code that a straight simple approach would have avoided.
I mean absolutely nothing disparaging against the folks that posted the code and they are both workable solutions. It’s good to see different ways of accomplishing the same thing. They just reminded me of my pet peeve and I'm venting. Maybe it’s just too many years of seeing code that could launch a rocket in a simple accounting system.
I get your point but, that is why we have comments. I will take performance over easy to read any day of the week. Comments are easy to type and can make some incredibly complicated code easy to understand.
+1
August 24, 2012 at 1:02 pm
I get your point but, that is why we have comments. I will take performance over easy to read any day of the week. Comments are easy to type and can make some incredibly complicated code easy to understand.
I can't argue that. Do you think this:
SELECT SUBSTRING(@Source,PATINDEX('%[^'+@Char+'X]%',@Source),1000)
out performs this:
select REPLACE(LTRIM(REPLACE('000temp001', '0', ' ')), ' ', '0')
I'm also not arguing learning regular expressions helps on the resume. My point is very general and not really specific to anyone in particular here. I don't think anyone really disagrees with what I'm saying. Which is "when all other things are equal, keep it simple".
I'm coming from an IBM mainframe background, long ago, and when you come in and the middle of the night to fix an issue that someone made complicated just because they could it makes for a longer night. You also don't have time to stop and learn when the on-lines need to be up in a couple of hours.
I see the same thing in day to day support now that I support Windows applications.
Comments are great and really help. In most shops I've been in they are as common as documentation:w00t:
The same type of programmers that generate complicated esoteric code usually make statements like "you shouldn't be programming if you can't read code". Usually they are excellant programmers that have forgotten not everyone is at the same skill level.
In a business envronment with frequent turnover it is very important to use sructured easy to understand code. I'm not saying inefficient code.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply