substring query - 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)

substring query

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2008-03-05 : 16:36:06

name
--------------------------------------
copd_update_sept2007 9/25/2007 3:24:50 PM
copd_update_sept2007 9/26/2007 1:18:07 PM
feb2008_v2_n2 2/25/2008 1:56:41 PM
feb2008_v2_n2 2/26/2008 2:28:05 PM

i want string like till '' blank spaces..

how can i get that
select substring(name,1,15) from mymaxiddate

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 16:43:35

what?

do you just want the date only out of the date time?

Poor planning on your part does not constitute an emergency on my part.

ayu
Starting Member

43 Posts

Posted - 2008-03-05 : 16:45:24

i want like:

name
--------------------------------------
copd_update_sept2007
copd_update_sept2007
feb2008_v2_n2 2/25/2008
feb2008_v2_n2 2/26/2008

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 16:56:53

[code]
Create TABLE #table (name varchar(100) not null)

Insert #table ([name])
Select 'copd_update_sept2007 9/25/2007 3:24:50 PM' UNION
Select 'copd_update_sept2007 9/26/2007 1:18:07 PM' UNION
Select 'feb2008_v2_n2 2/25/2008 1:56:41 PM' UNION
Select 'feb2008_v2_n2 2/26/2008 2:28:05 PM'

Select substring([name],1,charindex(' ',[name])-1) as Name
, right([name],len([name])-charindex(' ', [name])+1) as date
FROM #table

drop table #table

/*results
copd_update_sept2007 9/25/2007 3:24:50 PM
copd_update_sept2007 9/26/2007 1:18:07 PM
feb2008_v2_n2 2/25/2008 1:56:41 PM
feb2008_v2_n2 2/26/2008 2:28:05 PM
*/
[/code]

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:03:45

or

parsename(replace(col),' ','.'),1)

Madhivanan

Failing to plan is Planning to fail

ayu
Starting Member

43 Posts

Posted - 2008-03-06 : 08:25:22

thanks for replying dataguru1971 and madhivanan.

but i m not getting the results correct...

i have table - called updatelist

updateno. name udate
----------- ---------------------------------------------------------------------- -------------------------
70566 copd_update_n1v1 8/1/2007 4:19:22 PM 2007-08-01 16:21:11.360
71630 copd_update_n1v1 8/7/2007 11:08:57 AM 2007-08-07 11:09:35.237
71876 copd_update_n1v1 8/8/2007 10:39:35 AM 2007-08-08 10:40:56.300
84371 copd_update_sept2007 9/25/2007 3:24:50 PM 2007-09-25 15:26:58.557
84675 copd_update_sept2007 9/26/2007 1:18:07 PM 2007-09-26 13:18:47.237
94341 copd_update_Oct2007 10/23/2007 4:39:53 PM 2007-10-23 16:41:17.037
94592 copd_update_Oct2007 10/24/2007 10:47:26 AM 2007-10-24 10:48:08.077
108261 copd_update_Nov2007 11/27/2007 4:43:35 PM 2007-11-27 16:44:17.517
108740 copd_update_Nov2007 11/28/2007 2:42:20 PM 2007-11-28 14:42:55.780
145795 copd_update_Jan2008 1/16/2008 9:46:18 AM 2008-01-16 09:52:14.277
147862 copd_update_Jan2008 1/18/2008 4:56:07 PM 2008-01-18 16:56:59.977
150430 copd_update_Jan2008 1/22/2008 3:33:43 PM 2008-01-22 15:41:24.523
178421 feb2008_v2_n2 2/25/2008 1:56:41 PM 2008-02-25 14:02:42.930
179478 feb2008_v2_n2 2/26/2008 2:28:05 PM 2008-02-26 14:30:24.533
180618 feb2008_v2_n2 2/27/2008 10:19:06 AM 2008-02-27 10:19:50.547
181708 feb2008_v2_n2 2/28/2008 11:38:53 AM 2008-02-28 11:39:39.873
187335 hello message send 2008-03-05 13:46:56.593

(17 row(s) affected)

i want maxupdateno. - from each name.. iwant output like:

updateno. name udate
----------------------------
71876 copd_update_n1v1 8/8/2007 10:39:35 AM 2007-08-08 10:40:56.300
84675 copd_update_sept2007 9/26/2007 1:18:07 PM 2007-09-26 13:18:47.237
94592 copd_update_Oct2007 10/24/2007 10:47:26 AM 2007-10-24 10:48:08.077
108740 copd_update_Nov2007 11/28/2007 2:42:20 PM 2007-11-28 14:42:55.780
150430 copd_update_Jan2008 1/22/2008 3:33:43 PM 2008-01-22 15:41:24.523
181708 feb2008_v2_n2 2/28/2008 11:38:53 AM 2008-02-28 11:39:39.873
187335 hello message send 2008-03-05 13:46:56.593

(7 row(s) affected)

so instead of 17 i want 7rows...

can anyone help to figure it out.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-06 : 08:28:45

[code]SELECT t1.updateno,t1.name,t1.udate
FROM updatelist t1
INNER JOIN (SELECT MAX(updateno) AS MaxRec, name
FROM updatelist
GROUP BY name)t2
ON t2.name=t1.name
AND t2.MaxRec=t1.updateno[/code]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 08:31:43

quote:
Originally posted by ayu

i want like:

name
--------------------------------------
copd_update_sept2007
copd_update_sept2007
feb2008_v2_n2 2/25/2008
feb2008_v2_n2 2/26/2008

Based on this and your original request, how would we know that you would get results you didn't want?

You asked how to parse the name out of a field. We can help, but aren't mind readers.

Poor planning on your part does not constitute an emergency on my part.

ayu
Starting Member

43 Posts

Posted - 2008-03-06 : 08:45:04

thanks visakh16 but not getting max rows..instead of 7 getting 24rows..

and dataguru1971, that's why i need to seperate name column - parse the name out from the field.. so taht i can compare and from that i can get max updateno..that's the reason..

thanks for ur help..so i think in group by i have to use this parsename..

thanks all.

ayu
Starting Member

43 Posts

Posted - 2008-03-06 : 09:08:31

very close to resutls..btu still one row is not getting istead of 7 - 6r ggetting..

SELECT t1.updateno,t1.name,t1.udate
FROM updatelist t1
INNER JOIN (SELECT MAX(updateno) AS MaxRec, name
FROM updatelist
GROUP BY substring([name],1,charindex(' ',[name])-1)
)t2
ON t2.name=t1.name
AND t2.MaxRec=t1.updateno

i m getting:
updateno. name udate
----------------------------
71876 copd_update_n1v1 8/8/2007 10:39:35 AM 2007-08-08 10:40:56.300

94592 copd_update_Oct2007 10/24/2007 10:47:26 AM 2007-10-24 10:48:08.077
108740 copd_update_Nov2007 11/28/2007 2:42:20 PM 2007-11-28 14:42:55.780
150430 copd_update_Jan2008 1/22/2008 3:33:43 PM 2008-01-22 15:41:24.523
181708 feb2008_v2_n2 2/28/2008 11:38:53 AM 2008-02-28 11:39:39.873
187335 hello message send 2008-03-05 13:46:56.593

(6 row(s) affected)

sep2007 row not getting..whhy?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-06 : 13:01:39

remove the join to Name and remove Name from the derived table:

SELECT 
	t1.updateno,
	t1.name,
	t1.udate
FROM 
	@updatelist t1
INNER JOIN 
	(
		SELECT	
			MAX(updateno) AS MaxRec
		FROM 
			@updatelist 
		GROUP BY 
			substring([name],1,charindex(' ',[name])-1) 
	)AS t2
	ON t2.MaxRec=t1.updateno

-- Or the 2005 way
SELECT 
	updateno,
	name,
	udate
FROM
	(
		SELECT	
			updateno,
			name,
			udate,
			ROW_NUMBER() OVER(PARTITION BY SUBSTRING([name], 1, CHARINDEX(' ', [name]) - 1) ORDER BY  updateno DESC) AS RowNum
		FROM 
			@updatelist 
	) AS Temp
WHERE
	RowNum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources