March 8, 2013 at 12:52 am
Hi All,
I am using SQL server 2008. In my database I have a table sat "Employee" having a column say "Height" now in that cocolumnaeave value along with the units e.g. 5ft or 65in like this. Now I want to write a query that will seseparateut the value on the basis of test and numeric value. Something like this:
Select Height[text] as HeightValue, Height[numeric] as HeightUnit from Employee
and I should get the result as
HeightValue HeightUnit
5 ft
65 in
Something like the above example.
Can we have such query? Please suggest.
Regards,
Girish Nehte
March 8, 2013 at 2:41 am
one way
CREATE FUNCTION [dbo].[RemoveCharsVARCHAR](@Input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX('%[^0-9]%',@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^0-9]%',@Input)
END
RETURN @Input
END
GO
CREATE FUNCTION [dbo].[RemoveNumbersVARCHAR](@Input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX('%[0-9]%',@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[0-9]%',@Input)
END
RETURN @Input
END
GO
DECLARE @Employee TABLE (Height VARCHAR(10))
INSERT INTO @Employee VALUES ('5ft'),('65in')
SELECT dbo.RemoveCharsVARCHAR(Height) AS HieghtValue, dbo.RemoveNumbersVARCHAR(Height) AS HeightValue
FROM @Employee
GO
Results in
HieghtValueHeightValue
5ft
65in
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply