July 9, 2009 at 2:06 pm
guys need help. Some of the data from the table are
--
create table test( name varchar(29))
insert into test (name) values
('PERRING, JULIA M ') ,
('KILGORE, SHANTA N '),
('OSMAN, TARINA M '),
('SCHAUFELBERGER , DEB'),
('THANEY, ELIZABETH I '),
('RIVET, PAMELA S '),
('LADOLCE, AIMEE '),
('DUDLEY, ERIN P '),
('THOMAS, TIFFANY A ') ,
('SMITH, LAURA M '),
('ALSTON, RASHIDAH D ')
SELECT * FROM Test
now I need to select name in two different ways -
SELECT name,
substring(name,CHARINDEX(' ', name)+ 1 ,len(name)) as Secondnamepart, -- --- this one is OK
substring(name,CHARINDEX(' ', name)+ 1 ,len(name))
+' '+ substring(name,1,1)+ '.' As NameTag -- problem here, tired so many ------things
FROM name
-- desired results "lastname" space firstnameInitial dot
-- something like 'PERRING, JULIA M into JULIA P.
-- 'LADOLCE, AIMEE into AIMEE L.
DROP TABLE test
thanks
July 9, 2009 at 2:19 pm
guys was able to do it with this piece of code. But still love to have smaller one is possible.
SUBSTRING(substring(@name,CHARINDEX(' ', @name)+ 1 , len(@name)), 1, CHARINDEX(' ', LTRIM(substring(@name,CHARINDEX(' ', @name)+ 1 , len(@name)) )))
thanks
July 9, 2009 at 2:22 pm
Here's what I've got for this. Relies on you having a Numbers table, which is just a table with integers in it, usually from 1 to 10,000.
if object_id(N'tempdb..#test') is not null
drop table #test;
create table #test
(id int identity primary key,
name varchar(29));
insert into #test (name)
select ('PERRING, JULIA M ') union all
select ('KILGORE, SHANTA N ') union all
select ('OSMAN, TARINA M ') union all
select ('SCHAUFELBERGER, DEB') union all
select ('THANEY, ELIZABETH I ') union all
select ('RIVET, PAMELA S ') union all
select ('LADOLCE, AIMEE ') union all
select ('DUDLEY, ERIN P ') union all
select ('THOMAS, TIFFANY A ') union all
select ('SMITH, LAURA M ') union all
select ('ALSTON, RASHIDAH D ');
;with CTE as
(select
id,
row_number() over (partition by id order by number) as segmentnumber,
substring(name + ' ', Number, charindex(' ', name + ' ', Number) - Number) namesegment
from dbo.Numbers
inner join #test
on Number <= len(name)
where substring(' ' + name, Number, 1) = ' ')
select C2.namesegment + ' ' + left(C1.namesegment, 1) + '.'
from CTE C1
inner join CTE C2
on C1.id = C2.id
and C1.segmentnumber = 1
and C2.segmentnumber = 2;
This seems to work. I had to modify it because the insert statement you have only works in SQL 2008 and I'm still using 2005.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2009 at 2:23 pm
Not smaller but maybe a little bit more simple to maintain:
; WITH cte (name, fname) AS
(
SELECT
name,
SUBSTRING(name,CHARINDEX(' ', name) + 1 ,len(name))
FROM @test-2
)
SELECT
SUBSTRING(fname, 1, CHARINDEX(' ', fname, 1)) + ' ' + LEFT(name, 1)
,*
FROM cte
How to Post Data/Code to get the best Help How to Post Performance Problems
July 9, 2009 at 2:37 pm
thanks guys............But looks like I was not clear on my questions and postings.
All I needed was name in two different formats. I have a field in a tbl called EName where it will be stored as lastname, Firstname MI like "JOHNSON, TOMMY D ". All i was trying to do was
get name in this two format "TOMMY D" as last name part
and "TOMMY J." as nametag.
July 9, 2009 at 2:39 pm
select RTRIM(SUBSTRING(name,charindex(',',name)+1,len(name)))+'.' from test
July 9, 2009 at 2:53 pm
sarvesh thanks, But looks like again I was not clear. OK
let me try to explain here in more detail. I have a table with column name as clerk_name where data will be like this lastname, firstname MI (if any). Something of like this
SINGH, SARVESH
JOHNSON, TOMMY D
KHADKA, BHANADUR R
KALAPANI, TOLT
now i need to insert these values to two other tbls were name will be stored as like this
for one column it will be like secondnamepart
SARVESH
TOMMY D
BHANADAR R
TOLT
and for another column it will be firstname LastnameIN as nametag. like
SARVESH S.
TOMMY J.
BHANADAR K.
TOLT K.
two of this statemnst give sthis
CONVERT(varchar(20), SUBSTRING(substring(clerk_name,CHARINDEX(' ', clerk_name)+ 1 , len(clerk_name)), 1, CHARINDEX(' ', LTRIM(substring(clerk_name,CHARINDEX(' ', clerk_name)+ 1 , len(clerk_name))))) +' '+ substring(clerk_name,1,1)+ '.' ) AS NameTag,
CONVERT(varchar(20), SUBSTRING(QA.Clerk_Name,CHARINDEX(' ', QA.clerk_name)+1,len(QA.clerk_name))) AS SecondNamePart
July 10, 2009 at 3:53 am
hope this one helps u
SELECT name,
substring(name,CHARINDEX(' ', name)+ 1 ,len(name)) as Secondnamepart,
substring(name,CHARINDEX(' ', name)+ 1 ,charindex(' ',substring(name,CHARINDEX(' ', name)+ 1,len(name))))
+' '+ substring(name,1,1)+ '.' As NameTag
FROM test
July 10, 2009 at 6:56 am
Greetings,
Here is some code that will first break your name into 3 separate pieces. Then, it will take the 3 pieces and put them back together as needed for your new tables.
DECLARE @FirstName varchar(40)
DECLARE @MiddleName varchar(40)
DECLARE @LastName varchar(40)
DECLARE @FirstComma int
DECLARE @SecondSpace int
SELECT
@FirstComma = CHARINDEX(',', Clerk_Name)
SELECT
@SecondSpace = CHARINDEX(' ', (@FirstComma + 2))
IF (@SecondSpace = 0)
BEGIN
SELECT
@SecondSpace = LEN(Clerk_Name) + 1
END
SELECT
@LastName = SUBSTRING(Clerk_Name, 1, (@FirstComma - 1)),
@FirstName = SUBSTRING(Clerk_Name, (@FirstComma + 2), (LEN(Clerk_Name) - (@FirstComma + 1) - (LEN(Clerk_Name) - @SecondSpace + 1)))
IF @SecondSpace < LEN(Clerk_Name)
BEGIN
SELECT
@MiddleName = SUBSTRING(Clerk_Name, (@SecondSpace + 1), (LEN(Clerk_Name) - @SecondSpace + 1))
END
ELSE
BEGIN
SELECT
@MiddleName = ''
END
INSERT INTO Table1
SELECT
@LastName + @MiddleName
INSERT INTO Table2
SELECT
@LastName + ' ' + SUBSTRING(@FirstName, 1, 1) + '.'
Have a good day.
Terry Steadman
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply