Spliting a cloumn 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

SQL Server 2005 Forums

Transact-SQL (2005)

Spliting a cloumn help

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-01-03 : 01:41:25

Hi i have a table with one column as below

Table A

Column1

test1=222;test2=333;test123=1
test546=432;test5435=3453
sample=23
.
.
.
so on.........

my requirement is..i need to split the column value,( take the integer value present at the end of the string) and insert it in another table as below

Table B
1
3453
23
..
..
..
..
Column2

integer values always follow an "=". and there can be multiple or single "=" in the column separated by ";". i need only the values after the last "=" symbol..
the column also accepts nulls..

please help me build a query for doing the above
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-03 : 01:56:32

[code]DECLARE @Test TABLE
(Column1 varchar(100)
)

INSERT INTO @Test VALUES ('test1=222;test2=333;test123=1')
INSERT INTO @Test VALUES ('test546=432;test5435=3453')
INSERT INTO @Test VALUES ('sample==23')

SELECT REVERSE(LEFT(REVERSE(Column1),CHARINDEX('=',REVERSE(Column1))-1)) FROM @Test[/code]

/*
---Output---

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

----------------------------------------------------------------------------------------------------
1
3453
23

(3 row(s) affected)
*/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-03 : 02:01:27

SELECT RIGHT(Col1, CHARINDEX('=', REVERSE(Col1)) - 1)
FROM Table1

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

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-01-07 : 04:07:09

quote:
Originally posted by Peso

SELECT RIGHT(Col1, CHARINDEX('=', REVERSE(Col1)) - 1)
FROM Table1

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

its give me the following error...
Invalid length parameter passed to the RIGHT function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-07 : 04:21:26

It means that not all records do have a "=" sign, or that I have changed the parameters for the CHARINDEX function.
Look up CHARINDEX in Books Online and see if the parameters are in correct order.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources