string manipulation - 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 manipulation

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-08-29 : 05:04:26

Hi If i have a string like :

AX4030303022

in one field and need to split it into

AX 030303022

I can use LEFT(field_name,2) for the 1st one, but
what can i use for the 2nd ?
and how can i deal with NULLS ?
if its NULL i want a blank space..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 05:08:00

SELECT ISNULL(LEFT(Col1, 2), '') AS Part1,
ISNULL(SUBSTRING(Col2, 3, 8000), '') AS Part2
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-29 : 05:09:24

Select
Case when Isnull(columnname,'') = '' then ''
Else
Left(columnname,2) + ' ' + Substring(columnname, 3, Len(columnname) - 2)
End
From tablename

--------------------------------------------------
S.Ahamed

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-08-29 : 05:10:06

ok I just saw that
[CODE]
declare @string as varchar(20)

set @string = 'AX9282828282'

select LEFT(@string,2) as x
select RIGHT(@string,(len(@string) -2))
[/CODE]

this works as well .. as a matter of interest what are the 3 & 8000 args for in SUBSTRING ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 05:14:45

3 is start at the third position
8000 is to take the next 8000 characters, or until string is ended. SUBSTRING is explained in your best friend, the help file Books Online.

SUBSTRING is more efficient than RIGHT (LEN-), because there are less aritmethics involved.
Also, I believe, RIGHT(LEN) will fail if string has trailing spaces.

declare @string as varchar(20)

set @string = 'AXy28282828. '

-- pazzy
select	LEFT(@string,2) as part1,
	RIGHT(@string,(len(@string) -2)) as part2

-- peso
select	LEFT(@string,2) as part1,
	substring(@string, 3, 8000) as part2

E 12°55'05.25"
N 56°04'39.16"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-08-29 : 09:23:37

I like stuff


declare @string as varchar(20)

set @string = 'AXy28282828. '

-- pazzy
select	LEFT(@string,2) as part1,
	RIGHT(@string,(len(@string) -2)) as part2

-- peso
select	LEFT(@string,2) as part1,
	substring(@string, 3, 8000) as part2

-- seventhnight
select	LEFT(@string,2) as part1,
	stuff(@string, 1, 2, '') as part2

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-29 : 09:33:28

In case if this is seperating alphbets and digits, then


declare @string as varchar(20)

set @string = 'AXy28282828. '

select LEFT(@string,patindex('%[0-9]%',@string)-1) as part1,
	stuff(@string, 1, patindex('%[0-9]%',@string)-1, '') as part2

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-08-29 : 09:49:07

Ok i also want it a set length .. but when i use for example :
[CODE]
select CAST(ISNULL(LEFT(ct_id_sponsor, 2), '')as varchar(30)),
CAST(ISNULL(SUBSTRING(ct_id_sponsor, 3, 8000), '')as varchar(30))
[/CODE]

So even with the 1st field is only 2 chars long, there should be 28 blank right ?
but on the text file its outputting to (this is a DTS) it's just writing the chars without the blanks..
but strangely the NULLS are being saved as blanks ..
AM i wrong using the CAST like this should i use CONVERT?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-29 : 09:55:08

1 Varchar wont store trailing spaces
2 Yes. NULLs are exported as empty string in Text file.

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-08-29 : 10:42:12

ok so what will store trailing spaces ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 10:47:43

[code]SELECT RIGHT(' ' + ISNULL(LEFT(ct_id_sponsor, 2), ''), 2) AS Part1,
RIGHT(REPLICATE(' ', 28) + ISNULL(SUBSTRING(ct_id_sponsor, 3, 28), ''), 28) AS Part2[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 11:03:42

Isn't there some issue with ISNULL munging datatypes (which COALESCE doesn't do)?

Not sure if it will effect this, but now you have started appending spaces etc. it might ...

(Actually I think the issue is that ISNULL forces the datatype of the first argument, which in this case would be fine, a dn if so I'll go back to sleep now!!)

Kristen

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-29 : 11:44:52

quote:

select LEFT(@string,patindex('%[0-9]%',@string)-1) as part1,

Mahdivan, Can you explain what this is doing? I am new to the patindex function and I am a bit confused by the [0-9].

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 12:06:30

[0-9] matches a single digit at that position.

Kristen

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-29 : 13:20:31

I see, very nice idea Madhivanan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-30 : 02:22:00

quote:
Originally posted by Zoroaster

I see, very nice idea Madhivanan

I am happy you spelled my name correctly

Madhivanan

Failing to plan is Planning to fail

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-31 : 10:25:12

quote:
Originally posted by madhivanan

quote:
Originally posted by Zoroaster

I see, very nice idea Madhivanan

I am happy you spelled my name correctly

Madhivanan

Failing to plan is Planning to fail

Sorry about the first time, I think I must have converted your name to Farsi!

Words are but constrained thoughts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources