SUBSRTINGing - 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

SUBSRTINGing

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-06 : 12:51:24

[code]I have the following select which should separate IP from the name of the same column
210.163.34.7 AZSDCVD
210.165.1.2 CAZAZAZL # SWAT
[/code]
[code]SELECT
t1,substring(t2,patindex('%[A-Za-z0-9.-]%',t2),datalength(t2))

FROM
(
SELECT
substring(value1,1,PATINDEX('%[^0-9.]%',value1)-1) AS t1,
STUFF(value1,1,PATINDEX('%[^0-9.'+char(32)+''+char(9)+']%',value1),'') AS t2
FROM
table
) s
result of above Query:
IP | NAME
210.163.34.7
210.165.1.2 CAZAZAZL
Desired results:
IP | NAME
210.163.34.7 AZSDCVD
210.165.1.2 CAZAZAZL[/code]

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 13:25:11

I am not seeing the output you posted when I run the query using your sample data. Probably because the white spaces got changed from tab to something else or vice versa when I copied.

In any case, here is the example code that I was trying out with your data. The second query is building from the first.

CREATE TABLE #tmp (value1 VARCHAR(255));
INSERT INTO #tmp VALUES 
('210.163.34.7   AZSDCVD'),
('210.165.1.2	CAZAZAZL	# SWAT')

--- 1
SELECT 
	t1,LTRIM(RTRIM(REPLACE(value1,t1,'')))
FROM
	(
	    SELECT
			substring(value1,1,PATINDEX('%[^0-9.]%',value1)-1) AS t1,
			value1
	    FROM
			#tmp
	) s 
	
--- 2
SELECT 
	t1,
	REPLACE(LTRIM(RTRIM(REPLACE(STUFF(value1,COALESCE(NULLIF(CHARINDEX('#',value1),0),LEN(value1)),LEN(value1),''),t1,''))),CHAR(9),'')
FROM
	(
	    SELECT
			substring(value1,1,PATINDEX('%[^0-9.]%',value1)-1) AS t1,
			value1
	    FROM
			#tmp
	) s

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-06 : 14:22:08

[code]Thanks.
the real data is cming from a host file.
The difference between the first and second line.
--1
210.163.34.7---AZSDCVDRETURN

--2
210.165.1.2TABCAZAZAZLTAB#-SWATRETURN
<<<<<<<<I JUST REPLACED THE TAB/SPACE with RED TO SHOW THEIR LOCATIONS>>>>>>>>
Both queries succeeded to only extract the IP but the name.
NOTE: I only need the string either between stapces or tabs or between space/tab.
eg:
210.165.1.2 SSSSSS # SWAT
210.165.1.2 ZZZZZZ
210.165.1.2 XXXXXX # SWAT
210.165.1.2 CAZAZA ASDFAS KASHFD
ouput:

SSSSSS
ZZZZZZ
XXXXXX
CAZAZA [/code]

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 15:03:32

Attempt #2 (a.k.a. rationalization of complexity)

CREATE TABLE #tmp (value1 VARCHAR(255));
INSERT INTO #tmp VALUES 
('210.163.34.7   AZSDCVD'),
('210.165.1.2	CAZAZAZL	# SWAT')

--- 1
SELECT 
	t1,LTRIM(RTRIM(REPLACE(value1,t1,'')))
FROM
	(
	    SELECT
			substring(value1,1,PATINDEX('%[^0-9.]%',value1)-1) AS t1,
			value1
	    FROM
			#tmp
	) s;
	
--- 2
SELECT 
	t1,
	REPLACE(
		LTRIM(
			RTRIM(
				REPLACE(
					LEFT(value1, COALESCE(NULLIF(CHARINDEX('#',value1),0)-1,LEN(value1))),t1,''
				)
			)
		),CHAR(9),''
	)
FROM
	(
	    SELECT
			substring(value1,1,PATINDEX('%[^0-9.]%',value1)-1) AS t1,
			value1
	    FROM
			#tmp
	) s;

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-06 : 16:02:11

Thanks anways, none of them works :).

This is what I ended up doing.



SELECT t1,replace(replace(ltrim(rtrim(substring(t2,1,patindex('%['+char(9)+''+char(32)+']%',t2)))),char(9),''),char(32),'')
	
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 

However, there still some exception to work on.
Thanks again sunitabeck

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