String function help - 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

String function help

Author

Topic

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 12:47:23

Hi all,

I have used this tring to separate the contents of a field:

left('$messagedata',8),right('$messagedata',6)

It works fine if there are exactly 14 characters in the field. But sometimes I may get a field that is 12 characters long so instead of separate the contents into the first 8 to the left and then whatever id remaining is to the right, itr counts from right to lefts and includes some of the left hnd characters in the right hand string for example:

12345678123456
would be separated into "12345678" "1234546"

but if I got a field 123456781234 it would be separated like "12345678" "781234" instead of "12345678" "1234"

I want to be able to specify a variable right section with a maximun say of 6 so it would separate the left into a eight strictly and the right into a maximum of six but could be as little as 1.

ny ideas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 12:49:43

[code]left('$messagedata',8),substring('$messagedata',9,len('$messagedata'))[/code]

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 12:56:17

quote:
Originally posted by visakh16

left('$messagedata',8),substring('$messagedata',9,len('$messagedata'))

Thanks, is the 9 specifying counting to the right from the ninth digit or specifying a variable maximum?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 13:00:27

nope. it designates start from 9th digit from left.
have a look at description of substring below

http://doc.ddart.net/mssql/sql70/setu-sus_17.htm

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 13:24:19

quote:
Originally posted by visakh16

nope. it designates start from 9th digit from left.
have a look at description of substring below

http://doc.ddart.net/mssql/sql70/setu-sus_17.htm

Cool thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 13:25:47

You're welcome

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 14:08:27

Hi,

I am getting an Incorrect syntax near ')'

?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 14:11:42

show the code used please

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 14:16:33

INSERT INTO table3 (postcode, type, msgtype) VALUES (left('$messagedata',8),substring('$messagedata',9,len('$messagedata'))
,'$messagetype');

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 14:19:03

try like this:-


INSERT INTO table3 (postcode, type, msgtype) 
SELECT left('$messagedata',8),
substring('$messagedata',9,len('$messagedata')),
'$messagetype'

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 14:27:06

quote:

Originally posted by visakh16

try like this:-


INSERT INTO table3 (postcode, type, msgtype) 
SELECT left('$messagedata',8),
substring('$messagedata',9,len('$messagedata')),
'$messagetype'

Incorrect syntax error near the $messagedata 8 value, I think because the brackets have been removed from round the values, but they have been specified around the column names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 14:32:16

whats '$messagedata'? is it a variable?

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 14:38:00

quote:
Originally posted by visakh16

whats '$messagedata'? is it a variable?

Yes it is a variable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 14:46:41

Are you using ms sql server?In sql server variables are used with '@' prefix

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 14:55:59

quote:
Originally posted by visakh16

Are you using ms sql server?In sql server variables are used with '@' prefix

Yes I am, but I haven't had a problem with $ before, like I say my original script "left('$messagedata',8),right('$messagedata',6)" worked fine, but only with fields that had exactly 6 characters after the initial eight, so 12345678123456 was seperated into two fields 12345678 123456. Any more than six characters after the eight and it starts to mess up, all I want to do is replace "left('$messagedata',8),right('$messagedata',6)" with something that gives me a variable number to the right. Would replacing "6" with "varchar (6)" help do you think?

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-31 : 19:00:02

I managed to do it, thinking on now I may have miss-understood what advice you gave me:

left('$messagedata',8),substring('$messagedata',9,len('$messagedata'))

I didn't adapt your script to specify the length I wanted (Bit thick, sorry), I just chucked the whole thing in as it was, so now I have used:

left('$messagedata',8),substring('$messagedata',9,8))

Thanks once again, sorry about the confusion.

Best regards as always

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 02:06:52

No problem

glad that you sorted it out. Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources