Substring Function - 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

General SQL Server Forums

New to SQL Server Programming

Substring Function

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-24 : 11:16:34

SQL 6.5 version

Can someone help figure out a better way to write this Query?

select substring(value1,1, patindex('%[a-zA-Z]%',value1)-1) as IP, substring (value1,patindex('%[a-zA-Z]%',value1), (patindex('%[^a-zA-Z0-9]%',value1))-(patindex('%[a-zA-Z]%',value1)))
from import

Thanks

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 11:27:35

can we ask what you're trying to do first?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-24 : 11:32:52

Maybe this - but there might be something easier if we knew what the data looked like.

;with cte as
(
select value1, loc1 = patindex('%[a-zA-Z]%',value1)
loc2=patindex('%[^a-zA-Z0-9]%',value1)
frrom import
)
select IP = substring(value1,1, loc1-1) as IP,
substring (value1,loc1, loc2-loc1)
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-24 : 11:37:14

Sure,the following is some of my data on the column value1:

2.51.1.12	JNASDLDB1  #COMMENT                                                                                                                                                                                                                                        
222.11.111.21	10214SDF2                                                                                                                                                                                                                                          
01.521.121.511	ASDKFSOIUWE   end                                                                                                                                                                                                                                       
400.177.139.15	MAKCHJFASO

desired out put:


-----------------------------
IP                | MAchine |
-----------------------------
2.51.1.12           JNASDLDB1
222.11.111.21       10214SDF2                                                                                                                                                                                                                                          
01.521.121.511	    ASDKFSOIUWE
400.177.139.15	    MAKCHJFASO

THE ISSUES HERE IS THAT BETWEEN THE VALUE OF THE IP ADDRESS AND THE NAME, THERE COULD BE ONE OR MORE SPACE/TAB. THIS APPLIES TO THE NAMES TOO. AT THE END OF THE NAME OF EACH MACHINE THERE COULD BE ONE OR MORE SPACE/TABS OR NONE. I HOPE ITS CLEAR!

--------------------------
Joins are what RDBMS's do for a living

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-24 : 11:39:05

nigelrivett, THIS SELECT HAS TO BE COMPATIBLE TO SQL SERVER 6.5, 7 (2000), 2005 AND 2008.
THANKS

--------------------------
Joins are what RDBMS's do for a living

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-24 : 11:43:57

select IP = substring(value1,1, loc1-1) as IP,
substring (value1,loc1, loc2-loc1)
from (
select value1, loc1 = patindex('%[a-zA-Z]%',value1)
loc2=patindex('%[^a-zA-Z0-9]%',value1)
from import
) cte

but does this work on your data? I would expect to see . in the strings.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 11:44:20

May be instead of using the CTE, you can rearrange Nigel's query to use it as a subquery:

SELECT
	SUBSTRING(value1, 1, loc1 -1) AS IP,
	SUBSTRING(value1, loc1, loc2 -loc1)
FROM
	(
	    SELECT
	    	value1,
	    	loc1 = PATINDEX('%[a-zA-Z]%', value1),
	    	loc2 = PATINDEX('%[^a-zA-Z0-9]%', value1)
	    FROM
	    	import
	) s

I didn't think there were SQL 6.5 installations still around!!

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-24 : 12:11:09

The problem with both Selects is :

loc2 = PATINDEX('%[^a-zA-Z0-9]%', value1)

in the subQuery this will find a non-Digit or non-Letter character in the string starting from the begenning. the result will be .DOT
Then in the outer query the defference between loc2-loc1 results in an error because loc2 is < loc1.
can you guys see the problem?!
Thanks

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 13:08:33

This time, I have tested and it works - at least for your test samples:

CREATE TABLE #tmp(value1 VARCHAR(255));
INSERT INTO #tmp VALUES ('2.51.1.12	JNASDLDB1  #COMMENT ');                                                                                                                                                                                                                                       
INSERT INTO #tmp VALUES ('222.11.111.21	10214SDF2  ');                                                                                                                                                                                                                                        
INSERT INTO #tmp VALUES ('01.521.121.511	ASDKFSOIUWE   end    ');                                                                                                                                                                                                                           
INSERT INTO #tmp VALUES ('400.177.139.15	MAKCHJFASO');

SELECT
	t1,
	LEFT(t2,PATINDEX('%[^a-zA-Z0-9]%',t2+' ')) AS t2
	
FROM
	(
	    SELECT
			LEFT(value1,PATINDEX('%[^0-9.]%',value1)-1) AS t1,
			STUFF(value1,1,PATINDEX('%[^0-9.]%',value1),'') AS t2
	    FROM
			#tmp
	) s
	
DROP TABLE #tmp;

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-24 : 13:57:20

Works but, Want you to know that LEFT function in 6.5 caused some issues:

Incorrect syntax near word LEFT

Weird...euh
This is the substitute of your select:

SELECT
	t1,
	substring(t2,1,PATINDEX('%[^a-zA-Z0-9]%',t2+' ')) AS t2
	
FROM
	(
	    SELECT
			substring(value1,1,PATINDEX('%[^0-9.]%',value1)-1) AS t1,
			ltrim(STUFF(value1,1,PATINDEX('%[^0-9.]%',value1),'')) AS t2
	    FROM
			table
	) s

Thanks to all.

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources