• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Truncate data in a column?

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
mattkovo
Asked:
mattkovo
  • 2
1 Solution
 
mcv22Commented:

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
 
mcv22Commented:
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
 
SharathData EngineerCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now