Solved

Truncate data in a column?

Posted on 2011-02-18
3
502 Views
Last Modified: 2012-05-11
I have data in a column that looks like this:

dbCatalog=DBTEST&UserID=admin&StoreID=12&Group=RG_Name&

When I run a query, I just want to return the UserID (admin) in this scenario.

How can I do this?
0
Comment
Question by:mattkovo
  • 2
3 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 34930716

SELECT
	SUBSTRING(ColumnName, PATINDEX('%&UserID=%', ColumnName) + LEN('&UserID='), PATINDEX('%&StoreID=%', ColumnName) - PATINDEX('%&UserID=%', ColumnName) + LEN('&UserID='))
FROM
	TableName
WHERE
	PATINDEX('%&UserID=%', ColumnName) > 0

Open in new window

0
 
LVL 12

Expert Comment

by:mcv22
ID: 34930728
Actually,


SELECT
	SUBSTRING(ColumnName, PATINDEX('%&UserID=%', ColumnName) + LEN('&UserID='), PATINDEX('%&StoreID=%', ColumnName) - PATINDEX('%&UserID=%', ColumnName) - LEN('&UserID='))
FROM
	TableName
WHERE
	PATINDEX('%&UserID=%', ColumnName) > 0

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34930770
If possible, create a function to split the column values to tabular format and then do your search. Why I suggesting this is, if you have dbCatelog,UserID,StoreID and GroupID not in the same order everytime, then the query with PATINDX mentioned above would fail.
CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter char(1) = ' ')
RETURNS @Strings TABLE (position int IDENTITY PRIMARY KEY, value nvarchar(max)) AS 
  BEGIN 
DECLARE @index int
    SET @index = -1
  WHILE (LEN(@text) > 0)
  BEGIN
    SET @index = CHARINDEX(@delimiter , @text)
     IF (@index = 0) AND (LEN(@text) > 0)
  BEGIN 
 INSERT INTO @Strings VALUES (@text)
  BREAK 
    END
     IF (@index > 1)
  BEGIN
 INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END 
   ELSE 
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
 RETURN
    END
 GO

Open in new window

Use the above created function in the query.
select t3.col,t4.value
  from (
select *
  from @table t1
 cross apply dbo.fn_Split(col,'&') t2) t3 
 cross apply dbo.fn_Split(value,'=') t4
where t4.position = 2 and t3.value = 'UserID'

Open in new window

Tested with your sample data.
declare @table table(col varchar(200))
insert @table values ('dbCatalog=DBTEST&UserID=admin&StoreID=12&Group=RG_Name&')
insert @table values ('dbCatalog=DBTEST&StoreID=12&UserID=admin&Group=RG_Name&')
insert @table values ('dbCatalog=DBTEST&StoreID=12&Group=RG_Name&')
insert @table values ('dbCatalog=DBTEST&StoreID=12&Group=RG_Name&UserID=admin')

select t3.col,t4.value
  from (
select *
  from @table t1
 cross apply dbo.fn_Split(col,'&') t2) t3 
 cross apply dbo.fn_Split(value,'=') t4
where t4.position = 2 and t3.value = 'UserID'
/*
col	value
dbCatalog=DBTEST&UserID=admin&StoreID=12&Group=RG_Name&	admin
dbCatalog=DBTEST&StoreID=12&UserID=admin&Group=RG_Name&	admin
dbCatalog=DBTEST&StoreID=12&Group=RG_Name&UserID=admin	admin
*/

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question