Maybe a Simple SQL Question 
Author Message
 Maybe a Simple SQL Question

I have a char(150) column in a table that ALWAYS looks like the
following.....

"xxx...:xxx.....:xxx....:xxx...:yyyyyyyyyyyyyyyy,xxx............."

ie: a string of chars, followed by a colon, followed by a strings of chars,
followed by a colon, etc.... (this happens 4 times), then another string
of chars (denoted here by the yyyyyyyyyyyyyyy), then a comma, then another
string of chars - confused??

Anyway, what I need is a piece of Transact-SQL that will just return me
just the string of chars between the fourth colon and the comma (ie: the y's)

eg:

"bobs:Tjsdlf4kls:12345:38:Bob Smith, ISD:/home/isd/bobs:/bin/csh"
                         -----------

should return "Bob Smith"

Any ideas??

Shane

(PS: Yes! This is an entry out of the Unix password file.)



Sun, 02 Feb 1997 16:26:15 GMT
 Maybe a Simple SQL Question


: Anyway, what I need is a piece of Transact-SQL that will just return me
: just the string of chars between the fourth colon and the comma (ie: the y's)
:
: eg:
:
: "bobs:Tjsdlf4kls:12345:38:Bob Smith, ISD:/home/isd/bobs:/bin/csh"
:                          -----------

It's *REAL* ugly, but it should work...  Try putting it all in one select if you
really want to spend some time!  ;-)

                           -----------

declare










--

Bill Mallary, Sybase/UNIX/Powerbuilder consultant / Houston, TX

The opinions expressed are my own, and yes, they are for sale.



Mon, 03 Feb 1997 00:28:27 GMT
 Maybe a Simple SQL Question

Quote:
Glasheen, ISD) writes:

> I have a char(150) column in a table that ALWAYS looks like the
> following.....

> "xxx...:xxx.....:xxx....:xxx...:yyyyyyyyyyyyyyyy,xxx............."

> ie: a string of chars, followed by a colon, followed by a strings of
> chars,
> followed by a colon, etc.... (this happens 4 times), then another string
> of chars (denoted here by the yyyyyyyyyyyyyyy), then a comma, then
> another
> string of chars - confused??

> Anyway, what I need is a piece of Transact-SQL that will just return me
> just the string of chars between the fourth colon and the comma (ie: the
> y's)

> eg:

> "bobs:Tjsdlf4kls:12345:38:Bob Smith, ISD:/home/isd/bobs:/bin/csh"
>                          -----------
> should return "Bob Smith"
> Any ideas??

Yet one more reason for putting an M4 front-end on whatever SQL tool you
are using. M4 programming is almost pure FP (functional programming),
designed to create expressions that even SQL Server can evaluate:

If you want to understand what the following does, capture the output of M4
before it goes into ISQL; or look at the M4 man pages. Pardon all the
backslash escapes; the dollar ($) and backquote (`) characters are
interpreted by the shell, even inside a "HERE" document (<<END ... END).
The "compiled" expression that M4 produces is a bit horrendous, but it does
exactly what you asked. Embed that in a view or stored procedure, and
forget about it:

(m4 | isql -U$DBUSER -P$DBPASS) <<END
define(\`TAIL',\`ifelse(\$1,0,\$2,

\`TAIL(decr(\$1),substring(\$2,1+charindex(":",\$2),150))')')



        /* "Tail(n,s)" extracts fields n, n+1, ... from s */

go
END

Good luck!
--

                 or uunet!van-bc!rsoft!mindlink!Mischa_Sandberg
*-*-*-*-*-*-*-*-*-*-*
Engineers think equations are an approximation of reality.
Physicists think reality is an approximation of the equations.
Mathematicians never make the connection.



Tue, 04 Feb 1997 14:38:54 GMT
 Maybe a Simple SQL Question

Look up the SUBSTRING() function if you know the
exact location of the y's.


Quote:

> Path:

ngc!ngcgate!uunet!demos1!news.uni-stuttgart.de!news.belwue.de!zib-berlin.de!zrz
TU-Berlin.DE!netmbx.de!Germany.EU.net!EU.net!howland.reston.ans.net!agate!msui
nfo!harbinger.cc.monash.edu.au!yarrina.connect.com.au!warrane.connect.com.au!ma
cqbl.com.au!isd32!shaneg
Quote:

> Newsgroups: comp.databases.sybase
> Subject: Maybe a Simple SQL Question
> Date: 17 Aug 1994 08:26:15 GMT
> Organization: Macquarie Bank Ltd.
> Lines: 26
> Distribution: world


> NNTP-Posting-Host: isd32.macqbl.com.au

> I have a char(150) column in a table that ALWAYS looks like the
> following.....

> "xxx...:xxx.....:xxx....:xxx...:yyyyyyyyyyyyyyyy,xxx............."

> ie: a string of chars, followed by a colon, followed by a strings of chars,
> followed by a colon, etc.... (this happens 4 times), then another string
> of chars (denoted here by the yyyyyyyyyyyyyyy), then a comma, then another
> string of chars - confused??

> Anyway, what I need is a piece of Transact-SQL that will just return me
> just the string of chars between the fourth colon and the comma (ie: the y's)

> eg:

> "bobs:Tjsdlf4kls:12345:38:Bob Smith, ISD:/home/isd/bobs:/bin/csh"
>                          -----------

> should return "Bob Smith"

> Any ideas??

> Shane

> (PS: Yes! This is an entry out of the Unix password file.)



Wed, 05 Feb 1997 05:55:02 GMT
 Maybe a Simple SQL Question

writes:
Quote:

> Anyway, what I need is a piece of Transact-SQL that will just return me
> just the string of chars between the fourth colon and the comma (ie: the
y's)

> eg:

> "bobs:Tjsdlf4kls:12345:38:Bob Smith, ISD:/home/isd/bobs:/bin/csh"
>                          -----------
> Any ideas??

Just for laughs:

create table #I(val int)
insert #I values(0)
insert #I values(1)
select
val=1+A.val+2*B.val+4*C.val+8*D.val+16*E.val+32*F.val+64*G.val+128*H.val
into    #J
from    #I A, #I B, #I C, #I D, #I E, #I F, #I G, #I H
delete #J where val > 150
/* #J = {1,2,3...150} */

select substring(PASSWD.line, start.val,
                 charindex(",",
substring(PASSWD.line,start.val,151-start.val)))
from    PASSWD, #J start
where   start.val = (select min(val) from #J head
                     where substring(PASSWD.line,1,head.val)
                             matches "%:%:%:%:")
....
Well, it's a novel way to test the query optimizer :-)
--

                 or uunet!van-bc!rsoft!mindlink!Mischa_Sandberg
*-*-*-*-*-*-*-*-*-*-*
Engineers think equations are an approximation of reality.
Physicists think reality is an approximation of the equations.
Mathematicians never make the connection.



Thu, 06 Feb 1997 08:02:33 GMT
 Maybe a Simple SQL Question


   > Newsgroups: comp.databases.sybase
   > Subject: Maybe a Simple SQL Question
   > Date: 17 Aug 1994 08:26:15 GMT
   > I have a char(150) column in a table that ALWAYS looks like the
   > following.....
   >
   > "xxx...:xxx.....:xxx....:xxx...:yyyyyyyyyyyyyyyy,xxx............."
   >
   > ie: a string of chars, followed by a colon, followed by a strings of chars,
   > followed by a colon, etc.... (this happens 4 times), then another string
   > of chars (denoted here by the yyyyyyyyyyyyyyy), then a comma, then another
   > string of chars - confused??
   >
   > Anyway, what I need is a piece of Transact-SQL that will just return me
   > just the string of chars between the fourth colon and the comma (ie: the y's)
   >
   > eg:
   >
   > "bobs:Tjsdlf4kls:12345:38:Bob Smith, ISD:/home/isd/bobs:/bin/csh"
   >                          -----------
   >
   > should return "Bob Smith"
   >
   > Any ideas??
   >
   >
   > Shane
   >
   > (PS: Yes! This is an entry out of the Unix password file.)

With some tuning of the while condition logic, something like this
should do the trick. COMPLETELY UNTESTED.


as





begin



end
/* now trim past the target */


go

--
Rob Sargent                      s-mail: Dept. of Human Genetics    

phone: (801) 585-3388                    Eccles Genetics Bldg
phax :          -3833                    Salt Lake City, Utah  84112



Sun, 09 Feb 1997 22:55:29 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Maybe a simple SQL Question

2. Very simple SQL question ... or maybe not

3. simple (maybe) SQL question

4. maybe not a simple question

5. somewhat complicated SQL query (mmmhh, maybe simple after all)

6. SQL7, SIMPLE SIMPLE SIMPLE question

7. Simple update query failing(bug maybe?)

8. maybe it's very simple,but i can't find out

9. xp_sendmail questions (maybe another question)

10. Simple SQL Question for the SQL Gurus

11. Simple Question...hopefully simple answer

12. Simple SQL Server Data Access Question I-SQL


 
Powered by phpBB® Forum Software