Substring/Charindex revisited - 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/Charindex revisited

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 13:31:51

So i sought assistance from a colleague in the office here but he forgets that i'm not as skilled as him. Since he signs my checks, we'll let him hold that illusion.

I have a select statement courtesy of the assistance from another board member here which helps me find the rows which have a specific number of spaces in each:


SELECT   LEN(FNAME)- LEN(REPLACE(FNAME,' ',''))AS [Spaces], FNAME, ECSOID
FROM mni
WHERE LEN(FNAME)- LEN(REPLACE(FNAME,' ',''))= 1
SELECT LEN(FNAME)- LEN(REPLACE(FNAME,' ',''))AS [Spaces], FNAME, ECSOID
FROM mni
WHERE LEN(FNAME)- LEN(REPLACE(FNAME,' ',''))= 2
SELECT LEN(FNAME)- LEN(REPLACE(FNAME,' ',''))AS [Spaces], FNAME, ECSOID
FROM mni
WHERE LEN(FNAME)- LEN(REPLACE(FNAME,' ',''))= 3

That script provides me with three result sets. with those three result sets, i default back to my bad habits and hack the data the rest of the way in Excel by using this process:
--Save each resultset as a csv, open with excel, delimit the firstname column according to spaces. Concatenate an update/set script in excel with the following:
--For the first resultset: =Concatenate("Update MNI set Fname='",A1," and Mname='",B1," where ECSOID ='",C1,"'")
--For the second resultset: =Concatenate("Update MNI set Fname='",A1," and Mname='",B1," ",C1,"' where ECSOID ='",D1,"'")
--For the second resultset: =Concatenate("Update MNI set Fname='",A1," and Mname='",B1," ",C1," ",D1,"' where ECSOID ='",E1,"'")
--Copy the concatentated column for each result set into a new query and execute against the new MNI table and the new ARR_PER table

I've been assured that it's possible to avoid moving the data out to Excel and create an update/set series of commands to update the Mname column using Charindex on the same principles as the select statement.

The goal here is to take everything after the first ' ' in the fname column and update the Mname column accordingly.

The following is what i'm trying to get, at least, the values with ONE space in the fname field to move the data after that space into the mname field:


UPDATE MNI SET mname=SUBSTRING(Fname,CHARINDEX(fname,' '),+1)

But i'm not getting results (nothing is updating, although the script is firing off w/o errors)

If i can get my head around the syntax for at least that first script, i'm hoping i can extrapolate it further for the rows with two and three spaces.

Any assistance is appreciated.

The structure of that table is below:


USE [DouglasConversion]
GO

/****** Object:  Table [dbo].[MNI]    Script Date: 03/15/2012 12:31:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MNI](
	[ID] [int] NOT NULL,
	[WarrantTypeID] [int] NULL,
	[CFSID] [int] NOT NULL,
	[WarrantNumber] [varchar](25) NULL,
	[WarrantStatusID] [int] NULL,
	[IssuedDate] [datetime] NULL,
	[InputDate] [datetime] NOT NULL,
	[CaseNumber] [varchar](50) NULL,
	[OTN] [varchar](50) NULL,
	[OffenseID] [int] NULL,
	[IncidentDateFrom] [datetime] NULL,
	[IncidentDateTo] [datetime] NULL,
	[MailedOutTo] [varchar](50) NULL,
	[MailedDate] [datetime] NULL,
	[MailReturnedDate] [datetime] NULL,
	[AssignedFugitiveDate] [datetime] NULL,
	[CourtDate] [datetime] NULL,
	[Misc] [text] NULL,
	[ReleaseDate] [datetime] NULL,
	[ReleaseWeek] [varchar](5) NULL,
	[RollNumber] [int] NULL,
	[DateEnteredNCIC] [datetime] NULL,
	[EnterNCICTypeID] [int] NULL,
	[DateCancelledNCIC] [datetime] NULL,
	[CancelNCICTypeID] [int] NULL,
	[CoDefendants] [varchar](255) NULL,
	[CheckNumber] [varchar](20) NULL,
	[ArrestDate] [datetime] NULL,
	[BondInfo] [text] NULL,
	[Magistrate] [varchar](25) NULL,
	[ReceivedDate] [datetime] NULL,
	[BondTypeID] [int] NULL,
	[BondRestriction] [bit] NULL,
	[ReceiptNumber] [varchar](20) NULL,
	[Agency] [varchar](50) NULL,
	[EFNumber] [varchar](20) NULL,
	[InCustody] [varchar](50) NULL,
	[HoldPlaced] [datetime] NULL,
	[ChargeTypeID] [int] NULL,
	[WarrantNarrative] [text] NULL,
	[Location] [char](255) NULL,
	[AgencyID] [int] NULL,
	[DivisionID] [int] NULL,
	[SeeNotes] [bit] NULL,
	[ExpireDate] [datetime] NULL,
	[LastUpdate] [datetime] NULL,
	[Judge] [varchar](25) NULL,
	[OutOfCounty] [bit] NULL,
	[EntryUserID] [int] NULL,
	[Notify] [bit] NULL,
	[TolledDate] [datetime] NULL,
	[MNIPRE] [varchar](9) NULL,
	[MNIPOST] [int] NOT NULL,
	[ArrestnoPre] [varchar](9) NULL,
	[WarrantID] [int] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[MiddleName] [varchar](50) NULL,
	[Suffix] [varchar](5) NULL,
	[DOB] [datetime] NULL,
	[RaceID] [int] NULL,
	[SexID] [int] NULL,
	[SSN] [varchar](10) NULL,
	[Aliases] [varchar](50) NULL,
	[Height] [varchar](10) NULL,
	[Weight] [varchar](10) NULL,
	[HairColorID] [int] NULL,
	[EyeColorID] [int] NULL,
	[DLN] [varchar](25) NULL,
	[DLStateID] [int] NULL,
	[DLExpireDate] [datetime] NULL,
	[Address] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[StateID] [int] NULL,
	[Zip] [varchar](10) NULL,
	[PhotoAttached] [bit] NULL,
	[PersonID] [int] NOT NULL,
	[TableID] [int] NULL,
	[OldID] [char](10) NULL,
	[MNINOPost] [int] NOT NULL,
	[MNINO] [varchar](15) NULL,
	[ECSOID] [varchar](15) NULL,
	[CREATED] [datetime] NULL,
	[CREATET] [varchar](5) NULL,
	[CREATEDBY] [varchar](10) NULL,
	[LNAME] [varchar](20) NULL,
	[FNAME] [varchar](15) NULL,
	[MNAME] [varchar](15) NULL,
	[TITLE] [varchar](5) NULL,
	[APPROX_AGE] [varchar](5) NULL,
	[RACE] [varchar](1) NULL,
	[SEX] [varchar](1) NULL,
	[HISPANIC] [bit] NULL,
	[HAIR] [varchar](3) NULL,
	[EYES] [varchar](3) NULL,
	[IDNO] [varchar](25) NULL,
	[IDSTATE] [varchar](2) NULL,
	[IDTYPE] [varchar](5) NULL,
	[B_CITY] [varchar](15) NULL,
	[B_COUNTY] [varchar](15) NULL,
	[B_STATE] [varchar](15) NULL,
	[B_NATION] [varchar](50) NULL,
	[OCA] [varchar](15) NULL,
	[SID] [varchar](15) NULL,
	[FBI] [varchar](15) NULL,
	[CITIZEN_OF] [varchar](50) NULL,
	[COUNTYBORN] [varchar](15) NULL,
	[BIRTHPLACE] [varchar](15) NULL,
	[NATION] [varchar](15) NULL,
	[PRINTCARD] [datetime] NULL,
	[ID_DATE] [datetime] NULL,
	[ID_OFFICER] [varchar](10) NULL,
	[ID_COMMENT] [varchar](30) NULL,
	[HENRY_TOP] [varchar](20) NULL,
	[HENRY_BOT] [varchar](17) NULL,
	[NCIC_FPC] [varchar](29) NULL,
	[LAST_MUG] [datetime] NULL,
	[LAST_NCIC] [datetime] NULL,
	[NCIC_ENTER] [datetime] NULL,
	[RUNAWAY] [bit] NULL,
	[RUNPRIOR] [bit] NULL,
	[MISSPARKID] [bit] NULL,
	[MISSFP] [bit] NULL,
	[MISSEND] [bit] NULL,
	[MISSINV] [bit] NULL,
	[MISSPER] [bit] NULL,
	[PHYCHALL] [bit] NULL,
	[MENCHALL] [bit] NULL,
	[I_REGCRIM] [bit] NULL,
	[I_REGSXPR] [bit] NULL,
	[I_CONSXOF] [bit] NULL,
	[I_CONFEL] [bit] NULL,
	[I_CONBURG] [bit] NULL,
	[I_PDUI] [bit] NULL,
	[I_CONAROB] [bit] NULL,
	[I_NARCVIO] [bit] NULL,
	[I_VIOLENT] [bit] NULL,
	[I_SHOCAP] [bit] NULL,
	[I_GANGMEM] [bit] NULL,
	[I_GANGASS] [bit] NULL,
	[I_PRISREL] [bit] NULL,
	[I_FTA] [bit] NULL,
	[I_LEO] [bit] NULL,
	[I_CWP] [bit] NULL,
	[I_DECEASED] [bit] NULL,
	[I_BAKERACT] [bit] NULL,
	[I_SUICIDE] [bit] NULL,
	[I_UNIVPRE] [bit] NULL,
	[I_PRIVATT] [bit] NULL,
	[I_STATEATT] [bit] NULL,
	[I_OTHER01] [bit] NULL,
	[I_OTHER02] [bit] NULL,
	[I_OTHER03] [bit] NULL,
	[I_OTHER04] [bit] NULL,
	[I_OTHER05] [bit] NULL,
	[I_OTHER06] [bit] NULL,
	[I_OTHER07] [bit] NULL,
	[I_OTHER08] [bit] NULL,
	[I_OTHER09] [bit] NULL,
	[I_OTHER10] [bit] NULL,
	[I_OTHER11] [bit] NULL,
	[I_OTHER12] [bit] NULL,
	[I_OTHER13] [bit] NULL,
	[I_OTHER14] [bit] NULL,
	[I_OTHER15] [bit] NULL,
	[I_OTHER16] [bit] NULL,
	[I_OTHER17] [bit] NULL,
	[I_OTHER18] [bit] NULL,
	[I_OTHER19] [bit] NULL,
	[I_OTHER20] [bit] NULL,
	[REGCRIM] [bit] NULL,
	[CAREERCRIM] [bit] NULL,
	[VIOLENT] [bit] NULL,
	[CAUTION] [bit] NULL,
	[SHO] [bit] NULL,
	[CONFELON] [bit] NULL,
	[PROB] [bit] NULL,
	[SEXPRED] [bit] NULL,
	[SEXOFFEND] [bit] NULL,
	[GANG_MEM] [bit] NULL,
	[GANG_AFF] [bit] NULL,
	[BURGLAR] [bit] NULL,
	[LEO] [bit] NULL,
	[WARACT] [bit] NULL,
	[WAROTH] [bit] NULL,
	[FI] [bit] NULL,
	[FI_REQUEST] [bit] NULL,
	[FIR_EXIST] [bit] NULL,
	[CIVCRI] [bit] NULL,
	[CIVENF] [bit] NULL,
	[CIVNON] [bit] NULL,
	[CIVINJ] [bit] NULL,
	[ARREST] [bit] NULL,
	[OFFENSE] [bit] NULL,
	[JAILBOOK] [bit] NULL,
	[JAILHIST] [bit] NULL,
	[TWARNING] [bit] NULL,
	[PAWN] [bit] NULL,
	[ALIAS] [bit] NULL,
	[SNAME] [bit] NULL,
	[VEHICLE] [bit] NULL,
	[OCCUP] [bit] NULL,
	[ORGAN] [bit] NULL,
	[ASSO] [bit] NULL,
	[CASEF] [bit] NULL,
	[FUTC] [bit] NULL,
	[PICTURE] [bit] NULL,
	[T_FACE] [bit] NULL,
	[T_NECK] [bit] NULL,
	[T_LARM] [bit] NULL,
	[T_RARM] [bit] NULL,
	[T_CHEST] [bit] NULL,
	[T_BACK] [bit] NULL,
	[T_LLEG] [bit] NULL,
	[T_RLEG] [bit] NULL,
	[T_RHAND] [bit] NULL,
	[T_LHAND] [bit] NULL,
	[S_FACE] [bit] NULL,
	[S_NECK] [bit] NULL,
	[S_LARM] [bit] NULL,
	[S_RARM] [bit] NULL,
	[S_CHEST] [bit] NULL,
	[S_BACK] [bit] NULL,
	[S_LLEG] [bit] NULL,
	[S_RLEG] [bit] NULL,
	[S_RHAND] [bit] NULL,
	[S_LHAND] [bit] NULL,
	[LEAR] [bit] NULL,
	[REAR] [bit] NULL,
	[LIP] [bit] NULL,
	[TONGUE] [bit] NULL,
	[LNIP] [bit] NULL,
	[RNIP] [bit] NULL,
	[NAVEL] [bit] NULL,
	[NOSE] [bit] NULL,
	[TC_FACE] [varchar](10) NULL,
	[TC_NECK] [varchar](10) NULL,
	[TC_LARM] [varchar](10) NULL,
	[TC_RARM] [varchar](10) NULL,
	[TC_CHEST] [varchar](10) NULL,
	[TC_BACK] [varchar](10) NULL,
	[TC_LLEG] [varchar](10) NULL,
	[TC_RLEG] [varchar](10) NULL,
	[TC_RHAND] [varchar](10) NULL,
	[TC_LHAND] [varchar](10) NULL,
	[SC_FACE] [varchar](10) NULL,
	[SC_NECK] [varchar](10) NULL,
	[SC_LARM] [varchar](10) NULL,
	[SC_RARM] [varchar](10) NULL,
	[SC_CHEST] [varchar](10) NULL,
	[SC_BACK] [varchar](10) NULL,
	[SC_LLEG] [varchar](10) NULL,
	[SC_RLEG] [varchar](10) NULL,
	[SC_RHAND] [varchar](10) NULL,
	[SC_LHAND] [varchar](10) NULL,
	[ACNE] [bit] NULL,
	[DARK] [bit] NULL,
	[LIGHT] [bit] NULL,
	[MEDIUM] [bit] NULL,
	[FRECKLED] [bit] NULL,
	[PALE] [bit] NULL,
	[POCKED] [bit] NULL,
	[RUDDY] [bit] NULL,
	[SALLOW] [bit] NULL,
	[TANNED] [bit] NULL,
	[NORMAL] [bit] NULL,
	[ACCENT] [bit] NULL,
	[LISP] [bit] NULL,
	[MUMBLES] [bit] NULL,
	[OFFENSIVE] [bit] NULL,
	[QUIET] [bit] NULL,
	[RAPID] [bit] NULL,
	[SLOW] [bit] NULL,
	[STUTTERS] [bit] NULL,
	[TALKATIVE] [bit] NULL,
	[NONENGLISH] [bit] NULL,
	[SLURRED] [bit] NULL,
	[THICKTONG] [bit] NULL,
	[BALD] [bit] NULL,
	[SHORT] [bit] NULL,
	[COLLAR] [bit] NULL,
	[SHOULDER] [bit] NULL,
	[LONG] [bit] NULL,
	[COARSE] [bit] NULL,
	[FINE] [bit] NULL,
	[THICK] [bit] NULL,
	[THINING] [bit] NULL,
	[WIRY] [bit] NULL,
	[AFRO_NAT] [bit] NULL,
	[BRAIDED] [bit] NULL,
	[BUSHY] [bit] NULL,
	[GREASY] [bit] NULL,
	[MILITARY] [bit] NULL,
	[PONYTAIL] [bit] NULL,
	[PROCESSED] [bit] NULL,
	[STRAIGHT] [bit] NULL,
	[WAVY_CURLY] [bit] NULL,
	[WIG] [bit] NULL,
	[FHUNK] [bit] NULL,
	[FHCLEANSHA] [bit] NULL,
	[FHFUMANCHU] [bit] NULL,
	[FHGOATEE] [bit] NULL,
	[FHLOWERLIP] [bit] NULL,
	[FHMUSTACHE] [bit] NULL,
	[FHFULLBEAR] [bit] NULL,
	[FHNONFUZZ] [bit] NULL,
	[FHFEMALE] [bit] NULL,
	[FHSIDEBURN] [bit] NULL,
	[FHUNSHAVEN] [bit] NULL,
	[FHVANDYKE] [bit] NULL,
	[FHOTHER] [bit] NULL,
	[AMPUTATION] [varchar](35) NULL,
	[OTHERFEAT] [varchar](35) NULL,
	[TEGOLD] [bit] NULL,
	[TEROTTEN] [bit] NULL,
	[TEFALSE] [bit] NULL,
	[TENONE] [bit] NULL,
	[TEMISSING] [bit] NULL,
	[TESTRAIGHT] [bit] NULL,
	[TEUNEVEN] [bit] NULL,
	[TEWHITE] [bit] NULL,
	[TEYELLOW] [bit] NULL,
	[TEBLACK] [bit] NULL,
	[TECHIPPED] [bit] NULL,
	[TEBRACES] [bit] NULL,
	[ECSOID_BAK] [varchar](15) NULL,
	[POB_Code] [varchar](2) NULL,
	[UNIQUEKEY] [varchar](22) NULL,
	[EYEGLASSES] [bit] NULL,
	[ETHNICITY] [int] NULL,
	[EYEBROW] [bit] NULL,
	[T_BUTTOCKS] [bit] NULL,
	[TC_BUTTOCKS] [varchar](10) NULL,
	[T_STOMACH] [bit] NULL,
	[TC_STOMACH] [varchar](10) NULL,
	[MUTE] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 13:38:10

hold the phone. Using:

UPDATE MNI SET mname=SUBSTRING(Fname,CHARINDEX(' ',fname)+1,DATALENGTH(fname))

i get results i need for the first run (thanks Tara)

Now i just need to continue that for the rows with Two spaces in fname.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 13:47:08

not so much. So while it's hitting the rows where there is at least one space in the cell and putting the next set of characters into the mname field, it is also, in cases where there are NO spaces in Fname, it is moving the whole value from Fname to Mname, duplicating the Fname in both fields. Obviously not what i want. So i need a where clause attached?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 13:58:19

i so enjoy having these conversations with myself ;)

UPDATE MNI SET mname=SUBSTRING(Fname,CHARINDEX(' ',fname)+1,DATALENGTH(fname)) WHERE fname NOT LIKE '[abc d]%'

Solves the next problem. Still stuck on addressing two spaces.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 14:18:13

Wow. Solved the issue. I can keep using the above solution, just applied to the MNAME field now, as it contains the full length of the remainder of the fname string, to pull the remaining data into the suffix and title fields.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 14:56:50

Ummm...WOW...somebody needs a class in data modeling and normalization here

WJ: did you inherit this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 15:01:59

..In any case...You can NOT Just blindly assume that 1 space means MI, 2 spaces means, what last name, 3 spaces means...????

We did some analysis JUST like this awhile ago right here..let me see if I can dig it up

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 15:21:50

It was you..I thought we proved you have to look at all the data, that you can't automate this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172147

I would dump out an ID with that values for the different space

Then make a list of ID's that for Space = 0 do nothing
Then make a list of ID's that for Space = 1 do Something

You have to check off and verify what ID's fallow the pattern you want, then clean up the data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 16:23:07

Ha! Yes it "was" me. In fact, what i've been able to do since then is clean the source data to the degree that i could rely on those number of spaces to represent what they were meant to represent. As a result i was able to use the approach above, with a few ghetto scripts in between to push the data into the right columns. This will actually be my first BIG data conversion where i'm not relying, at all, on any third party app nor dumping data out to excel or others to get from beginning to end. My next evolution will be to decrease the "number of moving parts" in my script and still get the job done.

It's days like these that i don't mind my job. Of course that could also be because the Gulf of Mexico is flat today and my kayak is waiting for me to get home in the next 30 mins.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources