April 30, 2009 at 11:11 pm
Hi,
I want to write a function in sql server which will tell whether the data in a column has a space or not.
Please help me.
It's very urgent.
May 1, 2009 at 2:00 am
I think you need to be a little more specific about what it is you are trying to do. Are you looking for columns with just a single space and nothing else or columns which could have other characters but a space embedded somewhere in the string? What if the column has other characters and multiple spaces in various locations? Does that qualify or does the column have to have one space and one space only?
If you can provide examples, that always helps.
Regards,
Mike
May 1, 2009 at 7:00 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[udf_contains_spaces] (@String VARCHAR (500))
RETURNS CHAR (1) AS
BEGIN
DECLARE @LenAS INT
DECLARE @ExitAS CHAR (1)
DECLARE @FoundAS CHAR (1)
DECLARE @LocAS INT
SET @Len= LEN(@String)
SET @Exit= 'N'
SET @Found= 'N'
SET @Loc= 1
WHILE @Exit = 'N'
BEGIN
IF SUBSTRING(@String,@Loc,1) = ' ' --CHECK FOR A SPACE
BEGIN
SET @Exit = 'Y'
SET @Found = 'Y'
END
ELSE IF @Len = @Loc --CHECK FOR END OF STRING
BEGIN
SET @Exit = 'Y'
END
ELSE --BUMP LOC TO NEXT CHARACTER TO CHECK
BEGIN
SET @Loc = @Loc + 1
END
END
RETURN(@Found)
END
Kev -=Conan The Canadian=-
@ConanTheCdn
May 1, 2009 at 7:01 am
So much for all my beautiful tabs to make it more readable!
Kev -=Conan The Canadian=-
@ConanTheCdn
May 1, 2009 at 7:05 am
Better yet, try playing around with something like this:
if 'asdf ssdfasf' like '% %' select 'hi!'
Kev -=Conan The Canadian=-
@ConanTheCdn
May 1, 2009 at 7:13 am
If this is indeed what he wants to do, what's wrong with using a built-in function like CHARINDEX?
Mike
May 1, 2009 at 7:19 am
That'a a very good idea! (This is one of those, why didn't I think of that moments!)
The last function I wrote this week, I needed the last location in a string of a alpha numeric character. So my brain on still thinking that way when I wrote out my original post. (It was just a bit different from that one)
Kev -=Conan The Canadian=-
@ConanTheCdn
May 1, 2009 at 7:40 am
Conan (5/1/2009)
That'a a very good idea! (This is one of those, why didn't I think of that moments!)The last function I wrote this week, I needed the last location in a string of a alpha numeric character. So my brain on still thinking that way when I wrote out my original post. (It was just a bit different from that one)
You might want to experiment with using CharIndex and Reverse to find the last location. It will take a little bit of math when you reverse it again, but I bet these built-in functions will blow that udf you wrote away.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 1, 2009 at 7:44 am
Conan (5/1/2009)
So much for all my beautiful tabs to make it more readable!
Hi Conan
Enclose your SQL with "code" tags:
[ code ]
SELECT * FROM Blah
[ /code ]
... without the spaces will appear as:
SELECT * FROM Blah
Maybe go back to your previous post, click "Edit" and just add the [ code ] and [ /code ] at start and end of your code.
Greets
Flo
May 1, 2009 at 2:41 pm
I usually use something like...
CHARINDEX(@valuetosearchfor,ColumnToSearch) > 0
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply