how to do a select with part of field

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)

how to do a select with part of field

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-05 : 08:06:17

Hi All,

i got a table with a field called name which has records like

Boston_1
Boston_2
Boston_3
New York_1
New York_2
New York_3
Texas_1
Texas_2

the question is how can i do select that just this?

Boston
New York
Texas

thanks a lot,

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 08:27:34

[code]
SELECT LEFT([name], CHARINDEX('_', [name])-1) AS [name]
FROM YourTable
[/code]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-05 : 08:38:22

SELECT DISTINCT LEFT([name], CHARINDEX('_', [name])-1) AS [name]
FROM YourTable

Jai Krishna

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-05 : 08:48:49

Thanks a lot guys! ;-)

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-05 : 09:05:22

Dam wasn't that easy, seems like i got old records that do not have the _ in them

What would be the work around?

tnx a lot

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-05 : 09:08:49

SELECT DISTINCT LEFT([name]+'_', CHARINDEX('_', [name]+'_')-1) AS [name]
FROM YourTable

Jai Krishna

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-02-05 : 09:09:00

Try this,

Samples
Select SUBSTRING('Boston_1', 1, (CHARINDEX('_', 'Boston_1')-1))
Select SUBSTRING('Name', 1, (CHARINDEX('_', 'Name')-1))

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-05 : 09:25:14

That seem to work, but is i got a distinct problem still

getting this as result

Boston
Boston
Boston
New York
New York
New York
Texas
Texas

insted of

boston
new york
texas

here my code

select DISTINCT [name], LEFT([name]+'_', CHARINDEX('_', [name]+'_')-1) AS [name],id,[language],[date],used from content_server.dbo.T_Varblocks
where type=@type and [language]=@lang

thansk a lot

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-02-05 : 09:54:34

Try this,
If you can provide data of all columns used in your query it will be easy to help

CREATE Table #Test
(
[Name] NVARCHAR(50)
)
INSERT INTO #Test Values ('BOSTON_1')
INSERT INTO #Test Values ('BOSTON_2')
INSERT INTO #Test Values ('BOSTON_3')
INSERT INTO #Test Values ('NEW YORK_1')
INSERT INTO #Test Values ('NEW YORK_2')
INSERT INTO #Test Values ('NEW YORK_3')
INSERT INTO #Test Values ('TEXAS_1')
INSERT INTO #Test Values ('TEXAS_2')

SELECT DISTINCT SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1) FROM #Test

SELECT SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1) FROM #Test Group BY SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1)

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-05 : 10:19:17

hi

i get aggragation error on the group

i go these fields
id (autonumbering)
[language] = varchar field contents is 'en'
[date] = just a dateitme field = standard SQL getdate()
used = bit field, 0 or 1

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 10:50:44

Is this what you're looking for?


SELECT DISTINCT
		CASE 
			WHEN CHARINDEX('_', [name]) > 0 THEN 
				LEFT([name], CHARINDEX('_', [name])-1) 
			ELSE [name]
		END AS [name]
FROM YourTable

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-05 : 11:01:11

i tryed it but still not getting distinct

here is my sql

SELECT DISTINCT
CASE
WHEN CHARINDEX('_', [name]) > 0 THEN
LEFT([name], CHARINDEX('_', [name])-1)
ELSE [name]
END AS [name], id,[language],[date],used
FROM content_server.dbo.T_Varblocks
where type=@type and [language]=@lang

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 12:14:35

[code]SELECT id,[language],[date],used
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY [date] DESC) AS Seq,
id,[language],[date],used
FROM
(
SELECT STUFF([name],CHARINDEX('_', [name]),LEN([name])-CHARINDEX('_', [name])+1,'') AS Name,
id,[language],[date],used
FROM content_server.dbo.T_Varblocks
where type=@type and [language]=@lang
)t
)r
WHERE Seq=1
[/code]

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-02-06 : 01:22:44

Try this,
select
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1) AS [name],
MAX(id),
[language],
MAX([date]),
used
from content_server.dbo.T_Varblocks
where type=@type and [language]=@lang
GROUP BY
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1),
[language],
used

Can you provide samples values of id, language, date, used, name and required output what you want. so that it will be easy to help you

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:44:25

quote:
Originally posted by Rajesh Jonnalagadda

Try this,
select
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1) AS [name],
MAX(id),
[language],
MAX([date]),
used
from content_server.dbo.T_Varblocks
where type=@type and [language]=@lang
GROUP BY
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1),
[language],
used

Can you provide samples values of id, language, date, used, name and required output what you want. so that it will be easy to help you

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

add this to sample data and then try

Boston

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-06 : 16:52:06

Hi Guys,

i tryed both but none worked

the first when i use the where, i doesn't anything.
the second didn't return anythings

here is some sample data

id, varblock, name, type, language, date, used
22453,Sample text,test_nl_1,states,nl,5-2-2009,10:46:35,0
22454,Sample text,test_pt_1,states,pt,5-2-2009 10:46:36,0
22455,Sample text,test_fr_1,states,fr,5-2-2009 10:46:38,0
22456,Sample text,test_de_1,states,de,5-2-2009 10:46:38,0
22457,Sample text,test_it_1,states,it,5-2-2009 10:46:39,0
22458,Sample text,test_es_1,states,es,5-2-2009 10:46:41,0
22459,Sample text,test_en_1,states,en,5-2-2009 10:46:41,0
22460,Sample text,test_en_2,states,en,5-2-2009 10:46:41,0
22461,Sample text,test_en_3,states,en,5-2-2009 10:46:41,0
22462,Sample text,test_en_4,states,en,5-2-2009 10:46:41,0

thanks a lot,

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-02-07 : 04:25:40

Try this,

CREATE TABLE #TEST
(
id INT,
varblock NVARCHAR(50),
[name] NVARCHAR(50),
[type] NVARCHAR(50),
[language] NVARCHAR(50),
date DATETIME,
used INT
)

INSERT INTO #TEST VALUES (22453,'Sample text','test_nl_1','states','nl','5-2-2009 10:46:35',0)
INSERT INTO #TEST VALUES (22454,'Sample text','test_pt_1','states','pt','5-2-2009 10:46:36',0)
INSERT INTO #TEST VALUES (22455,'Sample text','test_fr_1','states','fr','5-2-2009 10:46:38',0)
INSERT INTO #TEST VALUES (22456,'Sample text','test_de_1','states','de','5-2-2009 10:46:38',0)
INSERT INTO #TEST VALUES (22457,'Sample text','test_it_1','states','it','5-2-2009 10:46:39',0)
INSERT INTO #TEST VALUES (22458,'Sample text','test_es_1','states','es','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22459,'Sample text','test_en_1','states','en','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22460,'Sample text','test_en_2','states','en','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22461,'Sample text','test_en_3','states','en','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22462,'Sample text','test_en_4','states','en','5-2-2009 10:46:41',0)

select
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1) AS [name],
MAX(id),
[type],
[language],
MAX([date]),
used
from #TEST
where [type] = 'states' and [language]= 'en'
GROUP BY
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1),
[language],[type],
used

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:14:25

quote:
Originally posted by mike13

Hi Guys,

i tryed both but none worked

the first when i use the where, i doesn't anything.
the second didn't return anythings

here is some sample data

id, varblock, name, type, language, date, used
22453,Sample text,test_nl_1,states,nl,5-2-2009,10:46:35,0
22454,Sample text,test_pt_1,states,pt,5-2-2009 10:46:36,0
22455,Sample text,test_fr_1,states,fr,5-2-2009 10:46:38,0
22456,Sample text,test_de_1,states,de,5-2-2009 10:46:38,0
22457,Sample text,test_it_1,states,it,5-2-2009 10:46:39,0
22458,Sample text,test_es_1,states,es,5-2-2009 10:46:41,0
22459,Sample text,test_en_1,states,en,5-2-2009 10:46:41,0
22460,Sample text,test_en_2,states,en,5-2-2009 10:46:41,0
22461,Sample text,test_en_3,states,en,5-2-2009 10:46:41,0
22462,Sample text,test_en_4,states,en,5-2-2009 10:46:41,0

thanks a lot,

did you mean this?


SELECT id, varblock, name, type, language, date, used
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY STUFF(name,CASE WHEN CHARINDEX('_',name)>0 THEN CHARINDEX('_',name) ELSE LEN(name) END,CASE WHEN CHARINDEX('_',name)>0 THEN LEN(name)-CHARINDEX('_',name)+1 ELSE 0 END,'') ORDER BY date) AS Seq,
id, varblock, name, type, language, date, used
FROM Table
)t
WHERE Seq =1

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-07 : 12:56:18

quote:
Originally posted by Rajesh Jonnalagadda

Try this,

CREATE TABLE #TEST
(
id INT,
varblock NVARCHAR(50),
[name] NVARCHAR(50),
[type] NVARCHAR(50),
[language] NVARCHAR(50),
date DATETIME,
used INT
)

INSERT INTO #TEST VALUES (22453,'Sample text','test_nl_1','states','nl','5-2-2009 10:46:35',0)
INSERT INTO #TEST VALUES (22454,'Sample text','test_pt_1','states','pt','5-2-2009 10:46:36',0)
INSERT INTO #TEST VALUES (22455,'Sample text','test_fr_1','states','fr','5-2-2009 10:46:38',0)
INSERT INTO #TEST VALUES (22456,'Sample text','test_de_1','states','de','5-2-2009 10:46:38',0)
INSERT INTO #TEST VALUES (22457,'Sample text','test_it_1','states','it','5-2-2009 10:46:39',0)
INSERT INTO #TEST VALUES (22458,'Sample text','test_es_1','states','es','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22459,'Sample text','test_en_1','states','en','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22460,'Sample text','test_en_2','states','en','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22461,'Sample text','test_en_3','states','en','5-2-2009 10:46:41',0)
INSERT INTO #TEST VALUES (22462,'Sample text','test_en_4','states','en','5-2-2009 10:46:41',0)

select
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1) AS [name],
MAX(id),
[type],
[language],
MAX([date]),
used
from #TEST
where [type] = 'states' and [language]= 'en'
GROUP BY
[name],
SUBSTRING([Name], 1, CHARINDEX('_', [Name])-1),
[language],[type],
used

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

it does not return distinct record, it returns:

name name1 Column1 type language Column2 used
-------------------------------------------------- -------------------------------------------------- ------- -------------------------------------------------- -------------------------------------------------- ------- ------
test_en_1 test 22459 states en 2009-05 0
test_en_2 test 22460 states en 2009-05 0
test_en_3 test 22461 states en 2009-05 0
test_en_4 test 22462 states en 2009-05 0
(10 row(s) affected)
(4 row(s) returned)

it only should return one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 13:48:05

did you try mine?

mike13
Posting Yak Master

219 Posts

Posted - 2009-02-07 : 14:06:36

yeah yours is missing the where clause where [type] = 'states' and [language]= 'en'

i tried to enter but could figure out where

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources