seperating data from column - 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)

seperating data from column

Author

Topic

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 06:28:07

Hello,

My table structure is as follows :

Create table #UserList(userid nvarchar(20), stcod smallint) 

Insert INTO #UserList VALUES('State. : 1',0);

Insert INTO #UserList VALUES('Jack12',0);

Insert INTO #UserList VALUES('Tommy22',0);

Insert INTO #UserList VALUES('State. 2',0);

Insert INTO #UserList VALUES('Kathy25',0);

Insert INTO #UserList VALUES('State 4',0);

Insert INTO #UserList VALUES('Tim',0); 

Insert INTO #UserList VALUES('State.: 6',0); 

Insert INTO #UserList VALUES('Jimmy',0); 

Insert INTO #UserList VALUES('Boopla1',0); 

--select * from #UserList

The final data looks like this :

userid		stcod
State. : 1    	0
Jack12		0
Tommy22 	0
State. 2   	0
Kathy25		0
State 4		0
Tim		0
State.: 6	0
Jimmy		0
Boopla1		0

1. I want to first update the stcod. Select rows which start with 'State' in userid and pick the numeric value in that. Eg: In 1st row, the numericvalue is 1 and update the stcode with that value till u enounter another row starting with 'state'

2. After updating all rows, remove the rows which start with 'State'

Expected Output :

userid	       stcod	
Jack12	       1
Tommy22        1	
Kathy25        2
Tim            4
Jimmy          6
Boopla1        6

How to i write the query to do this?

Thank you for reading this.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:01:57

Its a bit messy in SQL Server.

if this is a one off job, for a smallish amount of data, I would put it in Excel, sort it out using a few Formulae, and then Import into SQL Server.

If you need to do it in SQL Server add an IDENTITY column to your table, so that the "order" is defined, and then you can find the next-earlier row which is like 'State %', grab the number from that to update the record. When that's done you can delete the "State" records.

Kristen

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 07:14:56

Hi,

I searched in google and tried the RIGHT and the charindex function but i feel this will not work...i need someone experienced to help me out with the query(i am too new to this)..i will take your suggestion and add the identity column.

I would appreciate if you could give me some code..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 07:22:48

Why did you store state id and user name in the same column?
All you need is Normalisation.
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:29:20

So we aren't doing an Excel solution then?

You need to guard against a real user's name starting with "State", but other than that this should do the trick


SELECT	U1.ID, U1.userid, U1.stcod,
	[State] = 
	(
		SELECT TOP 1 [State] = RIGHT(U2.userid, CHARINDEX(' ', REVERSE(U2.userid)))
		FROM	#UserList AS U2
		WHERE	    U2.ID < U1.ID
			AND U2.userid LIKE 'State[. ]%'
		ORDER BY U2.ID DESC
	)
FROM	#UserList AS U1
WHERE	U1.userid NOT LIKE 'State[. ]%'

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 07:29:56

[code]DECLARE @UserList TABLE (RowID INT IDENTITY, UserID NVARCHAR(20), StCod SMALLINT)

INSERT @UserList
SELECT 'State. : 1', 0 UNION ALL
SELECT 'Jack12', 0 UNION ALL
SELECT 'Tommy22', 0 UNION ALL
SELECT 'State. 2', 0 UNION ALL
SELECT 'Kathy25', 0 UNION ALL
SELECT 'State 4', 0 UNION ALL
SELECT 'Tim', 0 UNION ALL
SELECT 'State.: 6', 0 UNION ALL
SELECT 'Jimmy', 0 UNION ALL
SELECT 'Boopla1', 0

SELECT UserID,
REVERSE(LEFT(StCod, CHARINDEX(' ', StCod) - 1)) AS StCod
FROM (
SELECT ul.UserID,
REVERSE((SELECT TOP 1 x.UserID FROM @UserList AS x WHERE x.RowID < ul.RowID AND x.UserID LIKE 'State%' ORDER BY RowID DESC)) AS StCod
FROM @UserList AS ul
WHERE ul.UserID NOT LIKE 'State%'
) AS s[/code]

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

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:30:07

"Why did you store state id and user name in the same column?"

My guess is this is the raw output from something else, and we're in a Staging table. Hence my suggesting that clean-up in Excel first might be easiest!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 07:30:29

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

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 07:31:32

yes..kirsten you are correct..this is a staging table..EVERYONE, thanks..i will try all your queries.

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 07:42:20

Peso how to write your query as Update query?

i tried this :

Update @UserList
set Stcod = s.stCod
FROM (
SELECT ul.UserID,
REVERSE((SELECT TOP 1 x.UserID FROM @UserList AS x WHERE x.RowID < ul.RowID AND x.UserID LIKE 'State%' ORDER BY RowID DESC)) AS StCod
FROM @UserList AS ul
WHERE ul.UserID NOT LIKE 'State%'
) AS s

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:46:48

[code]
UPDATE U1
SET stcod =
-- SELECT U1.ID, U1.userid, U1.stcod, [State] =
(
SELECT TOP 1 [State] = RIGHT(U2.userid, CHARINDEX(' ', REVERSE(U2.userid)))
FROM #UserList AS U2
WHERE U2.ID < U1.ID
AND U2.userid LIKE 'State[. ]%'
ORDER BY U2.ID DESC
)
FROM #UserList AS U1
WHERE U1.userid NOT LIKE 'State[. ]%'
[/code]
Kristen

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 07:46:56

But on doing that update it gives me :
Conversion failed when converting the nvarchar value '1 : .etatS' to data type smallint.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:47:33

Then you can do:


DELETE U1
FROM	#UserList AS U1
WHERE	U1.userid LIKE 'State[. ]%'

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:48:39

Note: I didn't provide the change the your Create Table for the IDENTITY, herewith:


Create table #UserList
(
	ID	int identity(1,1) NOT NULL,
	userid nvarchar(20), 
	stcod smallint
)

Kristen

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 07:49:22

ok thanks kirsten..and how to write peso's query as update? I get an error

Conversion failed when converting the nvarchar value '1 : .etatS' to data type smallint.

when i try this :

Update @UserList
set Stcod = s.stCod
FROM (
SELECT ul.UserID,
REVERSE((SELECT TOP 1 x.UserID FROM @UserList AS x WHERE x.RowID < ul.RowID AND x.UserID LIKE 'State%' ORDER BY RowID DESC)) AS StCod
FROM @UserList AS ul
WHERE ul.UserID NOT LIKE 'State%'
) AS s

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:55:59

"and how to write peso's query as update?"

I ain't got time to do that as well as writing mine for you ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 07:56:14

[code]UPDATE s
SET s.StCod = (
SELECT TOP 1 RIGHT(x.UserID, CHARINDEX(' ', REVERSE(x.UserID)) - 1)
FROM @UserList AS x
WHERE x.RowID < s.RowID
AND x.UserID LIKE 'State%'
ORDER BY x.RowID DESC
)
FROM @UserList AS s
WHERE s.UserID NOT LIKE 'State%'[/code]

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 08:00:32

thanks again

i wrote this and got this:

Update @UserList
set Stcod = REVERSE(LEFT(s.StCod, CHARINDEX(' ', s.StCod) - 1)) 
FROM (
SELECT ul.UserID,
REVERSE((SELECT TOP 1 x.UserID FROM @UserList AS x WHERE x.RowID < ul.RowID AND x.UserID LIKE 'State%' ORDER BY RowID DESC)) AS StCod
FROM @UserList AS ul
WHERE ul.UserID NOT LIKE 'State%'
) AS s

select * from @UserList

and am getting output as :

1	State. : 1	1
2	Jack12	        6
3	Tommy22	        1
4	State. 2	6
5	Kathy25	        1
6	State 4	       6
7	Tim	       1
8	State.: 6	6
9	Jimmy	        1
10	Boopla1	         6

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 08:03:08

thanks kirsten..your query produces exact result..thanks so much for your time..

thank you peso.

ronedin
Starting Member

29 Posts

Posted - 2007-10-16 : 08:04:21

thanks peso for modifying your query..you guys are so helpful!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources