Substring / charindex - 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

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 13:26:43

So i want column B to be everything from column A which exists after the first space in Column A. It feels like it should be a substring, but something tells me charindex is involved. And that's as far as i'm getting.

help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-13 : 13:45:36

Show us a data example. Usually describing the problem isn't enough for us to visualize it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 13:57:18

[code]
Column A Column B
One Red Dog Red Dog
Four Open Doors Open Doors
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-13 : 13:59:10

That looks like your expected result. Is that entire row what it currently is for one column? So what we need is sample data plus the expected output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 14:00:05

Column A is the sample data, column B is the output i'm looking for.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 14:04:31

My shot in the dark looked like:


UPDATE MNI_ADD SET street = 
Case 
when CHARINDEX(' ', address) > 0 
then LEFT(address,CHARINDEX(' ', street)-1) else street

from dbo.MNI_ADD

where column A is address and column B is street.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-13 : 14:09:17

Here you go:

declare @s varchar(50)

set @s = 'One Red Dog'

select substring(@s, charindex(' ', @s) + 1, datalength(@s))

It's always easiest to work with variables first and then change it to a table and then finally change it to an update.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 14:16:34

thanks. got it. i can see how to work that into my update. I'm just having trouble seeing or understanding excactly what the charindex function is doing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-13 : 14:20:58

It's searching for the first occurrence of a space and outputs where it is at.

Run this to see it:
select charindex(' ', @s)

Then you want ColumnB to start one character later, so I added 1 to that number:
select charindex(' ', @s) + 1

Now we want the substring to start at the above and go to the end (datalength) and thus arrive at the solution I posted.

Hope this helps. We don't just write these out right away, we do them one step at a time and then combine them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 14:23:46

Thank you. 99% of the time just seeing the solution to a problem will allow me to see the mechanics behind it, but this was the other 1% for me for some reason.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-13 : 14:40:43

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources