extracting some text | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

extracting some text


dear all,
how can i extract some string in the following column
172.19.244.17
172.19.236.17
172.20.36.52
172.20.36.52
172.20.6.59 T
172.20.6.59 T
172.2.3.52 TR
172.20.47.59
172.19.232.15
172.19.234.59
172.19.238.59
advance thanks
Extract characters from string data by using functions such as SUBSTRING, and then store the result in a derived column. Review books online for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
This is an ideal case for PARSENAME. That’s what it is made for. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
A question to separate the men from the boys …
You’re watching to many commercials on TV. [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
parsename returns the specified part, but to extracte you need substring function anyway.
Maybe. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />I’m guessing, that the sample data <br /><br />172.20.36.52 <br />172.20.6.59 T<br />172.20.6.59 T<br />172.2.3.52 TR<br /><br />is a copy and paste error by using a non-fixed width font. If not, than you’re correct.<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Frank, there’s a Dutch proverb that translates as "die Ziegen von den Geiszen trennen" — if you’ll pardon my German.
I like your first phrase more. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />And, yes, I pardon your German. I even pardon that Germany did something that stupid as updating its spelling rules without any perceivable indication of a consistent and logical direction. [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br /><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
I was more concerned with the correct plural form – probably without the n in both cases, right?<br /><br />The forum didn’t accept my Ringel-S, but to be totally honest it was a Greek beta anyway.[<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]
The plurals are correct, imho. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
quote:Originally posted by rv62
dear all,
how can i extract some string in the following column
172.19.244.17
172.19.236.17
172.20.36.52
172.20.36.52
172.20.6.59 T
172.20.6.59 T
172.2.3.52 TR
172.20.47.59
172.19.232.15
172.19.234.59
172.19.238.59
advance thanks
Can you define some with expected result? Madhivanan Failing to plan is Planning to fail
[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />] Don’t you think it looks like a classroom based exercise.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />] Don’t you think it looks like a classroom based exercise.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes it is.<br />Thats why asking for <b>some</b> [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
]]>