Help with SQL query - 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

General SQL Server Forums

New to SQL Server Programming

Help with SQL query

Author

Topic

dean_w
Starting Member

2 Posts

Posted - 2012-06-01 : 13:41:45

Hi,
I need to remove live usernames from a couple of tables in a SQL database so that it can be sent to a 3rd party for analysis.

So for example I have 2 columns (UserKey and URI)

Row A: - UserKey = 1, URI = sip:userA@abc.com
Row B: - UserKey = 2, URI = sip:+4412345678@abc.com
Row C: - UserKey = 3. URI = sip:userC@abc.com
etc

Only the usernames need to be anonymised, not the telephone numbers.

So, I came up with:

UPDATE [qoemetrics].[dbo].[User]
SET URI=[UserKey] + 'abc.com'
WHERE URI LIKE '%sip:[a-z]%'

I wanted to remove the value for URI column, then take the numeric value for UserKey column (as this will always be unique) and append abc.com, but it fails because it won't join text to a numeric number.

I dont want it to change anything with a telephone number, hence the section to look for only [a-z]. So output I want is for example:

Row A: - UserKey = 1, URI = sip:1@abc.com
Row B: - UserKey = 2, URI = sip:+4412345678@abc.com (Unchanged)
Row C: - UserKey = 3, URI = sip:3@abc.com

Does anyone have a suggestion on how I can make this query work?

Thanks,
Dean

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 23:40:14

[code]UPDATE [qoemetrics].[dbo].[User]
SET URI=CAST([UserKey] AS varchar(5))+ 'abc.com'
WHERE URI LIKE '%sip:[a-z]%'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-02 : 11:00:14

quote:
Originally posted by visakh16

UPDATE [qoemetrics].[dbo].[User]
SET URI=CAST([UserKey] AS varchar(5))+ 'abc.com'
WHERE URI LIKE '%sip:[a-z]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Hi visakh 

your query eliminate the sip string .






create table #temp(UserKey  int,URI varchar(30))

insert into #temp(UserKey,URI)
select 1,'sip:userA@abc.com'
union all
select 2,'sip:+4412345678@abc.com'
union all 
select 3,'sip:userC@abc.com'



update #temp set URI = 'sip:'+ cast(UserKey as varchar(20))+'@abc.com'

where URI like 'sip:[a-z][A-Z]%'


select * from #temp

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 13:45:40

quote:
Originally posted by vijays3

quote:
Originally posted by visakh16

UPDATE [qoemetrics].[dbo].[User]
SET URI=CAST([UserKey] AS varchar(5))+ 'abc.com'
WHERE URI LIKE '%sip:[a-z]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visakh

your query eliminate the sip string .

create table #temp(UserKey int,URI varchar(30))

insert into #temp(UserKey,URI)
select 1,'sip:userA@abc.com'
union all
select 2,'sip:+4412345678@abc.com'
union all
select 3,'sip:userC@abc.com'

update #temp set URI = 'sip:'+ cast(UserKey as varchar(20))+'@abc.com'

where URI like 'sip:[a-z][A-Z]%'

select * from #temp

Vijay is here to learn something from you guys.

i just reused OPs suggestion itself
i only modified the casting part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dean_w
Starting Member

2 Posts

Posted - 2012-06-03 : 05:42:46

Great thanks, will give this a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources