Insert space char into results 1

[skatebkp]

Hi guys,

I am trying to find a way to insert a space to seperate the first name and last name in my column when I query the db,

currently my data is displayed this way:

Abauer
Jjones
Asmith

and I would like it this way:

A Bauer
J Jones
A Smith

Is it possible to execute this in a query.

 

[mikrom]

Use the SQL string functions.
For example this works for me on DB2 UDB

[COLOR=#6a5acd]with[/color] names (name) [COLOR=#6a5acd]as[/color] (                                
  [COLOR=#804040][b]select[/b][/color] [COLOR=#ff00ff]'Abauer'[/color]                                     
  [COLOR=#6a5acd]from[/color] sysibm.sysdummy1 [COLOR=#804040][b]union[/b][/color] [COLOR=#804040][b]all[/b][/color]                     
  [COLOR=#804040][b]select[/b][/color] [COLOR=#ff00ff]'Jjones'[/color]                                     
  [COLOR=#6a5acd]from[/color] sysibm.sysdummy1 [COLOR=#804040][b]union[/b][/color] [COLOR=#804040][b]all[/b][/color]                     
  [COLOR=#804040][b]select[/b][/color] [COLOR=#ff00ff]'Asmith'[/color]                                     
  [COLOR=#6a5acd]from[/color] sysibm.sysdummy1)                              
[COLOR=#804040][b]select[/b][/color]                                                
  name,                                               
  left(name,[COLOR=#ff00ff]1[/color]) concat [COLOR=#ff00ff]' '[/color] concat                      
  upper(left(substr(name,[COLOR=#ff00ff]2[/color],length(name)[COLOR=#ff00ff]-1[/color]),[COLOR=#ff00ff]1[/color])) concat 
  substr(name,[COLOR=#ff00ff]3[/color],length(name)[COLOR=#ff00ff]-2[/color]) [COLOR=#6a5acd]as[/color] name_new           
[COLOR=#6a5acd]from[/color] names

result

....+....1....+....2....+..    
NAME    NAME_NEW               
Abauer  A Bauer                
Jjones  J Jones                
Asmith  A Smith                
********  End of data  ********

 

[PHV]

currently my data is displayed this way
What is the SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skatebkp]

yeah mikrom that is what I am going for but without having to manually insert the names in the query, I presume by modifying your code slightly I could achieve this.


btw guys here is my code:

DECLARE @status CHAR(10)

SET @DateNow=convert(CHAR(10),getdate(),120)

SET @status='online'

SELECT REPLACE([ADEReport].[dbo].[User].[JID],'@bmwlds1mun.sei-it.net',' ')as Agent

,MIN(convert (varchar(10),DATEADD(Hour,2,ChangeTime),108)) as TimeLoggedIn

      FROM [ADEReport].[dbo].[PresenceChange]JOIN [ADEReport].[dbo].[User]

    ON [ADEReport].[dbo].[PresenceChange].[UID] = [ADEReport].[dbo].[User].[UID]

    AND CONVERT(CHAR(10),ChangeTime,120) = @DateNow

    AND PresenceStatus = @status

  group by JID

  order by JID asc";

 

[skatebkp]

Ok after a slight modification of your code it worked perfectly , never thought of using string functions in SQL, it's actually quite practical.

thanx for you help.

(upper(left([ADEReport].[dbo].[User].[JID],1))+' '+upper(left(substring([ADEReport].[dbo].[User].[JID],2,len([ADEReport].[dbo].[User].[JID])-1),1))+ substring([ADEReport].[dbo].[User].[JID],3,len([ADEReport].[dbo].[User].[JID])-2