substring return string from '-', to last 2 digits

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

SQL Server 2005 Forums

Transact-SQL (2005)

substring return string from '-', to last 2 digits

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-03 : 15:48:10

String = 'mystring is cool - nola la'

return 'mystring is cool' as name,
return 'nola' as city,
return 'la' as state

--need to remove last two chars on city part

SELECT DISTINCT 
                      LEFT(pickat, CHARINDEX('-', pickat + '-') - 1) AS company, SUBSTRING(pickat, CHARINDEX('-', pickat + '-') + 2, LEN(pickat)) AS city, RIGHT(RTRIM(pickat), 
                      2) AS state
FROM         dbo.BOA_HoustonNorm

--This returns - 'Hercules' 	'Kenedy TX'	'TX'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-03 : 15:59:12

this?

declare @d varchar(100)

set @d =  'mystring is cool - nola la'

select rtrim(left(@d,charindex('-',@d) - 1)) as [name]
,ltrim(rtrim(substring(@d,(charindex('-',@d) + 1) ,(len(@d) - charindex('-',@d) - 2)))) as [city]
,ltrim(rtrim(right(@d,2))) as [state]

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-03 : 16:20:26

i get this error

Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-03 : 16:23:20

it was a record didnt have any chars after the '-'

thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-03 : 16:24:23

What should be the result for those scenarios?

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-09-03 : 17:51:26

I need some way to say if the field is empty then use LEN(column) if > 0 then LEN(column)-2

case ?
select rtrim(left(@d,charindex('-',@d) - 1)) as [name]
,ltrim(rtrim(substring(@d,(charindex('-',@d) + 1) ,(len(@d) - charindex('-',@d) - 2)))) as [city]
,ltrim(rtrim(right(@d,2))) as [state]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-04 : 00:03:53

Hi Try this once,

DECLARE @STRING VARCHAR(255)
SELECT @STRING = 'MYSTRING IS COOL - NOLA LA'

SELECT PARSENAME(REPLACE(@STRING,'-','.'),2) AS 'NAME',
PARSENAME(REPLACE(PARSENAME(REPLACE(@STRING,'-','.'),1),' ','.'),2) AS 'CITY',
PARSENAME(REPLACE(PARSENAME(REPLACE(@STRING,'-','.'),1),' ','.'),1) AS 'STATE'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources