Strange substring problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Strange substring problem

Author

Topic

djheath
Starting Member

7 Posts

Posted - 2009-08-11 : 07:03:56

Hi, I am relatively new to MS SQL. Could someone help with this problem I have?

Basically, I am passing a VarChar String into a function, checking if the string contains two particular characters and if so setting a variable gw_type based on those characters, before doing some work based on its type and then returning a result.

The start of the function "fnIsActiveGateway" looks like this:


----
FUNCTION [dbo].[fnIsActiveGateway](@name [varchar](16))

----

DECLARE @const_active tinyint, @const_inactive tinyint, @const_not_exists tinyint;
	SET		@const_active = 0;
	SET		@const_inactive = 1;
	SET		@const_not_exists = 2;
	
	-- Default return value is not exists
	DECLARE @retval int
	SET @retval = @const_not_exists;

	DECLARE	@GW_Type varchar(10);


	--
	--  What type of GW have we got?
	IF (SUBSTRING(@name,7,2) = 'GW')
		SET @GW_Type = 'Hard';
	ELSE IF (SUBSTRING(@name,7,2) = 'BG')
		SET @GW_Type = 'Bureau';
	ELSE IF (SUBSTRING(@name,7,2) = 'SG')
		SET @GW_Type = 'Soft';
	ELSE IF (SUBSTRING(@name,7,2) = 'VG')
		SET @GW_Type = 'Virtual';
	ELSE IF (SUBSTRING(@name,1,2) = 'DC')
		SET @GW_Type = 'DataCentre';
	ELSE
		SET @GW_Type = 'Unknown';

It then goes on to do perform relevant work based on the GW_Type.
The first 4 checks, "GW" to "VG" all work fine when I pass in a relevant string, but the DataCentre part doesn't.

If I run the code:

DECLARE @gw_name varchar(16)
SET     @gw_name = 'DC1-ECGW-2001'

DECLARE @retval INTEGER;

SELECT  @retval=CIB.dbo.fnIsActiveGateway(@gw_name);
PRINT   @retval

Then the gw_type is never set.

However, if I remove the first 1 off the name of the name so set the name to be
'DC-ECGW-2001' then the correct result is achieved.

I am doing a substring on the first and second characters of the string so why should it care what the third character is?

Any help would be great.

Thanks.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-11 : 07:33:47

The code that u have posted seems fine.Is there any other processing being done in the function? Can you post the entire function as you have it.

--------------------
Rock n Roll with SQL

djheath
Starting Member

7 Posts

Posted - 2009-08-11 : 07:51:43

Thanks,

Here is the whole function:


USE [CIB]
GO
/****** Object:  UserDefinedFunction [dbo].[fnIsActiveGateway]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnIsActiveGateway](@name [varchar](16))
RETURNS [int] WITH SCHEMABINDING, EXECUTE AS CALLER
AS 
BEGIN

DECLARE @const_active tinyint, @const_inactive tinyint, @const_not_exists tinyint;
	SET		@const_active = 0;
	SET		@const_inactive = 1;
	SET		@const_not_exists = 2;
	
	-- Default return value is not exists
	DECLARE @retval int
	SET @retval = @const_not_exists;

	DECLARE	@GW_Type varchar(10);


	--
	--  What type of GW have we got?
	IF (SUBSTRING(@name,7,2) = 'GW')
		SET @GW_Type = 'Hard';
	ELSE IF (SUBSTRING(@name,7,2) = 'BG')
		SET @GW_Type = 'Bureau';
	ELSE IF (SUBSTRING(@name,7,2) = 'SG')
		SET @GW_Type = 'Soft';
	ELSE IF (SUBSTRING(@name,7,2) = 'VG')
		SET @GW_Type = 'Virtual';
	ELSE IF (SUBSTRING(@name,1,2) = 'DC')
		SET @GW_Type = 'DataCentre';
	ELSE
		SET @GW_Type = 'Unknown';

	IF @GW_Type = 'Hard'
	BEGIN
		--  
		-- OK - we've got a Hard Gateway- so let's check out it's status
		IF EXISTS
		   (SELECT	1
			FROM	dbo.T_GATEWAY
			WHERE	name = @name)
		BEGIN
			-- OK so we've got a matching GW - let's work out if it's active or not
			IF EXISTS
   			   (SELECT	1 
	 			FROM	dbo.T_GATEWAY g
				WHERE	name = @name
				AND		dbo.fnIsActiveGatewayStatus(g.status_id) = 1)
				BEGIN
					SET @retval = @const_active;
				END
			ELSE
				BEGIN
					SET @retval = @const_inactive;
				END
		END
	END
	ELSE IF @GW_Type = 'Bureau'
	BEGIN
		--  
		-- OK - we've got a bureauGateway - so let's check out it's status
		IF EXISTS
		   (SELECT	1
			FROM	dbo.T_BUREAU_GATEWAY
			WHERE	name = @name)
		BEGIN
			-- OK so we've got a matching GW - let's work out if it's active or not
			IF EXISTS
   			   (SELECT	1 
	 			FROM	dbo.T_BUREAU_GATEWAY bg
				WHERE	name = @name
				AND		dbo.fnIsActiveBureauGatewayStatus(bg.status_id) = 1)
				BEGIN
					SET @retval = @const_active;
				END
			ELSE
				BEGIN
					SET @retval = @const_inactive;
				END
		END
	END
	ELSE IF @GW_Type = 'Soft'
	BEGIN
		--  
		-- OK - we've got a Soft Gateway - so let's check out it's status
		IF EXISTS
		   (SELECT	1
			FROM	dbo.T_SOFT_GATEWAY
			WHERE	name = @name)
		BEGIN
			-- OK so we've got a matching GW - let's work out if it's active or not
			IF EXISTS
   			   (SELECT	1 
	 			FROM	dbo.T_SOFT_GATEWAY sg
				WHERE	name = @name
				AND		dbo.fnIsActiveSoftGatewayStatus(sg.status_id) = 1)
				BEGIN
					SET @retval = @const_active;
				END
			ELSE
				BEGIN
					SET @retval = @const_inactive;
				END
		END
	END
	ELSE IF @GW_Type = 'Virtual'
	BEGIN
		--  
		-- OK - we've got a Virtual Gateway - so let's check out it's status
		IF EXISTS
		   (SELECT	1
			FROM	dbo.T_VIRTUAL_GATEWAY
			WHERE	name = @name)
		BEGIN
			-- OK so we've got a matching GW - let's work out if it's active or not
			IF EXISTS
   			   (SELECT	1 
	 			FROM	dbo.T_VIRTUAL_GATEWAY vg
				WHERE	name = @name
				AND		dbo.fnIsActiveVirtualGatewayStatus(vg.status_id) = 1)
				BEGIN
					SET @retval = @const_active;
				END
			ELSE
				BEGIN
					SET @retval = @const_inactive;
				END
		END
	END
	ELSE IF @GW_Type = 'DataCentre'
	BEGIN
		--  
		-- Always Active so return the active constant
		SET @retval = @const_active;
	END
	ELSE
	BEGIN
		-- We've got an unknown type
		SET @retval = @const_not_exists;
	END

	-- Return the result of the function
	RETURN @retval;

END

Running the following:

DECLARE @gw_name varchar(16)
SET     @gw_name = 'DC1-ECGW-2001';

DECLARE @retval INTEGER;

SELECT  @retval=CIB.dbo.fnIsActiveGateway(@gw_name);
PRINT   @retval

Returns 2 for not found or problem.

but running:

DECLARE @gw_name varchar(16)
SET     @gw_name = 'DC-ECGW-2001';

DECLARE @retval INTEGER;

SELECT  @retval=CIB.dbo.fnIsActiveGateway(@gw_name);
PRINT   @retval

Returns 0 for Active which is what I would expect.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-11 : 08:01:49

You are checking for 'Data Center' in your IF condition, while you are assinging 'DataCenter' in:
SET @GW_Type = 'DataCentre';

--------------------
Rock n Roll with SQL

djheath
Starting Member

7 Posts

Posted - 2009-08-11 : 08:05:11

quote:
Originally posted by rocknpop

You are checking for 'Data Center' in your IF condition, while you are assinging 'DataCenter' in:
SET @GW_Type = 'DataCentre';

--------------------
Rock n Roll with SQL

sorry that was a typo in the post, not in my code. I have corrected it now.

djheath
Starting Member

7 Posts

Posted - 2009-08-11 : 08:26:12

Ok, I think I have found the problem.

I have put the problem substring if statement at the beginning of the if block, so it now looks like this:

IF (SUBSTRING(@name,1,2) = 'DC')
		SET @GW_Type = 'DataCentre';
	ELSE IF (SUBSTRING(@name,7,2) = 'BG')
		SET @GW_Type = 'Bureau';
	ELSE IF (SUBSTRING(@name,7,2) = 'SG')
		SET @GW_Type = 'Soft';
	ELSE IF (SUBSTRING(@name,7,2) = 'VG')
		SET @GW_Type = 'Virtual';
	ELSE IF (SUBSTRING(@name,7,2) = 'GW')
		SET @GW_Type = 'Hard';
	ELSE
		SET @GW_Type = 'Unknown';

It seems the substring was checking from character 7 for 2, but then when it had to check from character 1 for 2, it couldn't go back along the string cos it had already started at 7 for the four previous if statements.

That seems a really stupid answer, but it's the only way I can get this code to work!
Thanks for you help.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-11 : 09:01:48

It seems to be a logical issue now, it all depends on what should be checked first. If you want 'DC' to be given priority then the change you have made is fine.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources