March 27, 2017 at 7:19 am
I have a query where I am using Pat index to remove zero's in the front.
Actual table data -
select ID from dbo.Employee
--In the actual table data will be
--00000000
--02005523
In order to remove zero's in the front of the data I used Patinde
select SUBSTRING(Emp.ID, PATINDEX('%[^0]%', Emp.ID+'.'), LEN(Emp.ID)) as EmpID from dbo.Employee Emp
After using this patindex now i see data like this
In place of 00000000 its showing as blank ie. its empty
2005523- this is second record that it is removing 0's in front.
All, I am looking is with out removing all these 00000000 , I want to remove only the very first zero in all the data.
How to modify this query?
March 27, 2017 at 7:29 am
mcfarlandparkway - Monday, March 27, 2017 7:19 AMI have a query where I am using Pat index to remove zero's in the front.Actual table data -
select ID from dbo.Employee
--In the actual table data will be
--00000000
--02005523In order to remove zero's in the front of the data I used Patinde
select SUBSTRING(Emp.ID, PATINDEX('%[^0]%', Emp.ID+'.'), LEN(Emp.ID)) as EmpID from dbo.Employee Emp
After using this patindex now i see data like this
In place of 00000000 its showing as blank ie. its empty
2005523- this is second record that it is removing 0's in front.All, I am looking is with out removing all these 00000000 , I want to remove only the very first zero in all the data.
How to modify this query?
What do you want the output to be when the value is '00000000'? Seems to me if this data is always numbers you should just convert it to an int.
_______________________________________________________________
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/
March 27, 2017 at 7:43 am
Right now the data is showing as
--00000000
--02005523
So with the patindex is there a way to remove only the first 0 from the data which i showed above?
In place of 00000000 , I want only 0000000 ; I have removed the first 0.
March 27, 2017 at 7:55 am
mcfarlandparkway - Monday, March 27, 2017 7:43 AMRight now the data is showing as--00000000
--02005523So with the patindex is there a way to remove only the first 0 from the data which i showed above?
In place of 00000000 , I want only 0000000 ; I have removed the first 0.
This should work
SELECT CASE WHEN Emp.ID LIKE '0%' THEN STUFF(Emp.ID, 1, 1, '') ELSE Emp.ID END AS EmpID
FROM dbo.Employee AS Emp;
March 27, 2017 at 7:56 am
Like this?CREATE TABLE #Emp (EmpID VARCHAR(8));
GO
INSERT INTO #Emp
VALUES ('00000000'),('02005523');
GO
SELECT *
FROM #Emp
UPDATE #Emp
SET EmpID = RIGHT(EmpID,7)
WHERE LEN(Empid) = 8
AND LEFT(EmpID,1) = 0;
SELECT *
FROM #Emp;
GO
DROP TABLE #Emp;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply